Forum Discussion
calculated column is returning zero instead of value
hello
i have created a calculate column that returns the percentage slap of a a measure. for example, if measure percentage = 120%, calculated column will return "120%-129%"
this is the calculated column dax
the output is always 0 for all measure[achiev of all] percentage values
any idea how to fix the dax?
please note that i have to create calculated columnn and not measure since i want to use the column to join with another table
Hi lawadaa ,
Based on your description, I created some data:
You can try using the following calculated columns:
Column Percentage slaps = SWITCH(TRUE, [achieve of overall]<0.9,"0%", [achieve of overall]<0.95,"90%-94.9%", [achieve of overall]<1.0,"95%-99.9%", [achieve of overall]<1.05,"100%-104.9%", [achieve of overall]<1.3,"120%-129.9%")It is important to note that the calculated column is not suitable for filtering, that is, the values in the calculated column are already calculated and do not change with the data, whereas the metric is filtered based on the context, and the value of the metric changes with the data, for example, in the example below a metric Measure Percentage slaps is created for the same dax:
When the viusal value changes, the data in the calculated column does not become, the measure changes:
You can check the following LINK for details:
Calculated Columns and Measures in DAX - SQLBI
If my answer doesn't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you
An attachment for your reference. Hope it helps.
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
lawadaa Try writing a switch statement instead and remove the last "0%"(the one as result of false for last if).
Hi lawadaa ,
Based on your description, I created some data:
You can try using the following calculated columns:
Column Percentage slaps = SWITCH(TRUE, [achieve of overall]<0.9,"0%", [achieve of overall]<0.95,"90%-94.9%", [achieve of overall]<1.0,"95%-99.9%", [achieve of overall]<1.05,"100%-104.9%", [achieve of overall]<1.3,"120%-129.9%")It is important to note that the calculated column is not suitable for filtering, that is, the values in the calculated column are already calculated and do not change with the data, whereas the metric is filtered based on the context, and the value of the metric changes with the data, for example, in the example below a metric Measure Percentage slaps is created for the same dax:
When the viusal value changes, the data in the calculated column does not become, the measure changes:
You can check the following LINK for details:
Calculated Columns and Measures in DAX - SQLBI
If my answer doesn't meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you
An attachment for your reference. Hope it helps.
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
