topic Re: calculated column is returning zero instead of value in DAX Commands and Tips

calculated column is returning zero instead of value

lawadaa — Fri, 06 Oct 2023 10:00:00 GMT

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

Re: calculated column is returning zero instead of value

ChiragGarg2512 — Fri, 06 Oct 2023 12:15:45 GMT

lawadaa Try writing a switch statement instead and remove the last "0%"(the one as result of false for last if).

Re: calculated column is returning zero instead of value

Anonymous — Mon, 09 Oct 2023 07:30:40 GMT

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 Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.