switch case formula as column in powerbi

[Anonymous]

i have crated the compliance status as shown in above screenshot but the value of actual is greater than min and max averages .I am not sure instead of 0 it is giving as 1 .If i implement same formula in as measure it is working fine . I am not sure what is the issue at the column level . I have given don't summarize when i drag the column at matrix visual .anyone can help here?

[v-xicai · Accepted solution]

Hi adithyanaini,

From your description, it looks like the calculated column and measure which use the same DAX returns different results when there is only one row displays in the table visual, right? Based on my test, if there is only one row in the table visual, the calculated column and measure which use the same DAX display the same result, as the both calculated column and measure are calculated based on the whole table rows. See:

If you add any other column field to the table visual then display more than one rows in the table visual, the measure will evaluate each row in table visual to calculate average values for columns [Actual Does], [Min Does]  and [Max Does], then return corresponding value o or 1 for each row based on the switch logic in the table visual. For the calculated column which uses the same DAX, when we put the calculated column in the table visual and set with “Don’t Summarize”, the column will display the same values as in Data view. See:  

 

Attachment is my Pbix test file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdwH48MP_wRMrPkqe4...

You can go through these two links to learn more about the difference between calculated column and measure:

http://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax

https://projectbotticelli.com/knowledge/dax-calculated-columns-vs-measures-video-tutorial

If you have any other issue, please feel free to ask.

Best Regards,

Amy

[v-xicai]

Hi adithyanaini,

From your description, it looks like the calculated column and measure which use the same DAX returns different results when there is only one row displays in the table visual, right?

Based on my test, if there is only one row in the table visual, the calculated column and measure which use the same DAX display the same result, as the both calculated column and measure are calculated based on the whole table rows. See:

 

 

If you add any other column field to the table visual then display more than one rows in the table visual, the measure will evaluate each row in table visual to calculate average values for columns [Actual Does], [Min Does]  and [Max Does], then return corresponding value o or 1 for each row based on the switch logic in the table visual. For the calculated column which uses the same DAX, when we put the calculated column in the table visual and set with “Don’t Summarize”, the column will display the same values as in Data view. See:  

 

Attachment is my Pbix test file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdwH48MP_wRMrPkqe4...

You can go through these two links to learn more about the difference between calculated column and measure:

http://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax

https://projectbotticelli.com/knowledge/dax-calculated-columns-vs-measures-video-tutorial

If you have any other issue, please feel free to ask.

Best Regards,

Amy