Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
lawadaa
Helper I
Helper I

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 

lawadaa_0-1696586148774.png

 

the output is always 0 for all measure[achiev of all] percentage values

 

 

lawadaa_1-1696586254538.png

 

 

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

1 ACCEPTED SOLUTION
v-tianyich-msft
Community Support
Community Support

Hi @lawadaa ,

Based on your description, I created some data:

vtianyichmsft_0-1696836223532.png

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%")

 

 

vtianyichmsft_1-1696836312788.png

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:

vtianyichmsft_2-1696836364326.png

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.

View solution in original post

2 REPLIES 2
v-tianyich-msft
Community Support
Community Support

Hi @lawadaa ,

Based on your description, I created some data:

vtianyichmsft_0-1696836223532.png

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%")

 

 

vtianyichmsft_1-1696836312788.png

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:

vtianyichmsft_2-1696836364326.png

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.

ChiragGarg2512
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors