Forum Discussion
Case Statement (Tableau) to DAX
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
3 Replies
- mahoneypat
Microsoft Employee
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
- amitchandak
Super User
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-power-query/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization Hi damjanv123 ,
You cna try this
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
