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.
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())
))))
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With