Case Statement (Tableau) to DAX

[damjanv123]

 I want to assign a numerical value to a categorical value (represented by text) and need to find the best way to do this in DAX/PowerBI. I've tried a few Switch commands but am getting errored out because I am combining text and numerical fields.

Essentially want to transform this Tableau case statement to something workable in DAX, The purpose of the statement is to assign a % to a categorical group.

case [age_group]
when 'AgeGRoup_0-4' then 0.062
when 'AgeGroup_5-9' then 0.065
when 'AgegRoup_10-14' then 0.059
when 'AgegRoup_15-19' then 0.060
when 'AgegRoup_20-24' then 0.065
when 'AgegRoup_25-29' then 0.070
when 'AgegRoup_30-34' then 0.072
when 'AgegRoup_35-39' then 0.067
when 'AgegRoup_40-44' then 0.067
when 'AgegRoup_45-49' then 0.066
when 'AgegRoup_50-54' then 0.065
when 'AgegRoup_55-59' then 0.063
when 'AgegRoup_60-64' then 0.056
when 'AgegRoup_65-69' then 0.051
when 'AgegRoup_70+' then 0.112

END

[harshnathani]

Hi @damjanv123 ,

You cna try this

https://community.powerbi.com/t5/Desktop/Display-the-Age-range-using-Power-BI-Standpoint-Online/td-p...

https://community.powerbi.com/t5/Desktop/Create-Aging-Bucket-formula-help/td-p/329705

https://www.sqlbi.com/articles/grouping-transactions-by-age-of-customers-in-dax/

Regards,

Harsh Nathani

[amitchandak]

@damjanv123 , You can try like

Switch([age_group]
, "AgeGRoup_0-4" , 0.062
, "AgeGroup_5-9" , 0.065
, "AgegRoup_10-14" , 0.059
, "AgegRoup_15-19" , 0.060
, "AgegRoup_20-24" , 0.065
, "AgegRoup_25-29" , 0.070
, "AgegRoup_30-34" , 0.072
, "AgegRoup_35-39" , 0.067
, "AgegRoup_40-44" , 0.067
, "AgegRoup_45-49" , 0.066
, "AgegRoup_50-54" , 0.065
, "AgegRoup_55-59" , 0.063
, "AgegRoup_60-64" , 0.056
, "AgegRoup_65-69" , 0.051
, "AgegRoup_70+" , 0.112
)

Switch(true()
, [age_group] ="AgeGRoup_0-4" , 0.062
, [age_group] ="AgeGroup_5-9" , 0.065
, [age_group] ="AgegRoup_10-14" , 0.059
, [age_group] ="AgegRoup_15-19" , 0.060
, [age_group] ="AgegRoup_20-24" , 0.065
, [age_group] ="AgegRoup_25-29" , 0.070
, [age_group] ="AgegRoup_30-34" , 0.072
, [age_group] ="AgegRoup_35-39" , 0.067
, [age_group] ="AgegRoup_40-44" , 0.067
, [age_group] ="AgegRoup_45-49" , 0.066
, [age_group] ="AgegRoup_50-54" , 0.065
, [age_group] ="AgegRoup_55-59" , 0.063
, [age_group] ="AgegRoup_60-64" , 0.056
, [age_group] ="AgegRoup_65-69" , 0.051
, [age_group] ="AgegRoup_70+" , 0.112
)

Also, refer

SEGMENTATION

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[mahoneypat]

If you are making a calculated column, an easier way may be with a groups column.  See this link.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

If not, see this link to use SWITCH(TRUE(), ... which can also be used to get your desired results.

https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat