Multiple IF Statements in DAX

if-statement powerbi dax
Rush picture Rush · Oct 26, 2016 · Viewed 47.8k times · Source

I currently have Column Data formulated below in Power BI which I need for it to display in one column but replacing the "1" with a Text value being:

Orginal column formula:

Age (18-27) = IF(AND([Age]>17, [Age]<28),"1",BLANK())
Age (28-35) = IF(AND([Age]>27, [Age]<36),"1",BLANK())
Age (36-43) = IF(AND([Age]>35, [Age]<44),"1",BLANK())
Age (44-50) = IF(AND([Age]>43, [Age]<51),"1",BLANK())
Age (50+) = IF([Age]>50,"1 ", BLANK())

Output:

Age (18-27) = IF(AND([Age]>17, [Age]<28),"Age (18-27)",BLANK())
Age (28-35) = IF(AND([Age]>27, [Age]<36),"Age (28-35)",BLANK())
Age (36-43) = IF(AND([Age]>35, [Age]<44),"Age (36-43)",BLANK())
Age (44-50) = IF(AND([Age]>43, [Age]<51),"Age (44-50)",BLANK())
Age (50+) = IF([Age]>50,"Age (50+) ", BLANK())

I would like to have the formula display the data in one column where it is consolidating the Output formula (seen above) so I see the results in one column.

Answer

user5226582 picture user5226582 · Oct 26, 2016

Just nest your IFs:

Age Group = IF(AND([Age]>17, [Age]<28),"18-27",
 IF(AND([Age]>27, [Age]<36),"28-35",
  IF(AND([Age]>35, [Age]<44),"36-43",
   IF(AND([Age]>43, [Age]<51),"44-50",
    IF([Age]>50,"50+", BLANK())
))))

Related questions

How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More
Python's equivalent of && (logical-and) in an if-statement

Here's my code: def front_back(a, b): # +++your code here+++ if len(a) % 2 == 0 && len(b) % 2 == 0: return a[:(len(a)/2)] + b[:(len(b)/2)] + a[(len(a)/2):] + b[(len(b)/2):] else: #todo! Not yet done. :P return I'm getting …

Read More
&& (AND) and || (OR) in IF statements

I have the following code: if(!partialHits.get(req_nr).containsKey(z) || partialHits.get(req_nr).get(z) < tmpmap.get(z)){ partialHits.get(z).put(z, tmpmap.get(z)); } where partialHits is a HashMap. What will happen if the …

Read More