CODEWITHSUNDEEP
if-statementpowerbidax
Rush
Rush

Reputation: 103

Multiple IF Statements in DAX

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.

Upvotes: 7

Views: 129931

Answers (4)

Alexis Olson
Alexis Olson

Reputation: 40204

Another variation of the SWITCH TRUE pattern:

Age Group =
SWITCH (
    TRUE (),
    [Age] <  18, BLANK (),
    [Age] <= 27, "18-27",
    [Age] <= 35, "28-35",
    [Age] <= 43, "36-43",
    [Age] <= 50, "44-50",
    [Age] >  50, "50+"
)

Upvotes: 2

user193130
user193130

Reputation: 8227

You can use SWITCH() like this which is much cleaner than nested IFs:

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

Source: https://community.powerbi.com/t5/Desktop/IF-or-SWITCH/m-p/167098#M72970

Upvotes: 11

sizo_abe
sizo_abe

Reputation: 501

if you want to categorize the column value in the numerical range you can use below dax query.

bubble = IF(AND([no_of_days_pending]>=100, [no_of_days_pending]<200),150,
 IF(AND([no_of_days_pending]>=200, [no_of_days_pending]<300),250,
  IF(AND([no_of_days_pending]>=300, [no_of_days_pending]<400),350,
   IF(AND([no_of_days_pending]>=400, [no_of_days_pending]<500),450,
    IF([no_of_days_pending]>=500,600, BLANK())
))))

Upvotes: -1

user5226582
user5226582

Reputation: 1986

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())
))))

Upvotes: 8

Related Questions