Column give incorrect result

[kennylch]

The last column "Healthy Level" gives incorrect result. As you can see the formula is like this. The text column created based on the value in a measure. 

The formula for the column is like this . But as you can see. it gives invalid result on this 

Healthy Level = SWITCH(

    True(),

    KFACTOR[Net Qty]>5, "Above Healthy",

    KFACTOR[Net Qty]>0, "Healthy Level",

    "Unhealthy Level"

)

You may download the bi file here for testing 

Test Data..pbix 

[Anonymous · Accepted solution]

Hi @kennylch ,

Thanks Idrissshatila , Ronald123  and SamInogic  for the quick reply. I have some other thoughts to add:
Since your parameter in SWITCH is a measure, and you create a calculated column, the result is unexpected. Measures are dynamic, and they're contextually influenced, so the results of the measure you see in the visual may not match the results of the measure that it's in a calculated column. So we need to create a measure:

Measure Healthy Level = SWITCH(
    True(),
    [Healthy]>5, "Above Healthy",
    [Healthy]>0, "Healthy Level",
    "Unhealthy Level"
)

Then set the filter in the filter pane.

Best Regards,

Neeko Tang

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

[kennylch]

That's right. I actually create another measure column and the result turns out correct. But the moment i insert this newly created measure column into matrix. THe matrix has repeating row issue. 

Please see the file 

Test Data modified.pbix 

[Anonymous]

Hi @kennylch ,

This is due to the fact that your visual objects come from fields in different tables, and because the metrics are affected by context there is a Cartesian product and therefore duplication. You can set [Req Qty (Kfactor x Plan Launch)] is not blank in the filter pane to achieve the correct result.

Best Regards,

Neeko Tang

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

[SamInogic]

Hi,

As per our understanding, you want to include the rows where Net Qty is equal to 5 in the "Above Healthy" category. To do this, we can update the DAX formula for the "Healthy Level" column like this:

Healthy Level =

SWITCH(

    TRUE(),

    'Table'[Net Qty] >= 5, "Above Healthy",

    'Table'[Net Qty] > 0, "Healthy Level",

    "Unhealthy Level"

)

Please refer to the screenshot given below,

Thanks!

[kennylch]

sorry. what i want is to use Healthy Qty. I forgot to point to Healthy Qty column instead. But if you change at ur end and you will notice is wrong as well

You may refer to the attached photo. As you can see i update the formula point to "heathy" column. The Heathy Level text column still doesnt display the correct text 

[Idrissshatila]

Hello @kennylch ,

can you please point out what are the invalid results, because as per your measure, the result is correct.

[kennylch]

hi.You are right. The measure number "Healthy" is correct with the calculation. But if you notice the PART : 00-200-1485. third row in the screenshot. Heathy is 0.76. It should be fall under "Unhealthy Level" in the healthy level column given the formula below. But it show "Above Healthy" instead. 

Healthy Level = SWITCH(

    True(),

    KFACTOR[Net Qty]>5, "Above Healthy",

    KFACTOR[Net Qty]>0, "Healthy Level",

    "Unhealthy Level"

)

[Idrissshatila]

Hello @kennylch ,

you're using in the dax the net qty and in the validation you're using another column.

the measure is correct, but if you want to compare the healthy column then you should include it in the dax instead of Net Qty.

[kennylch]

Hi. Thanks for point out. i have accidentally change the formula to netqty before send out. You may try to point to healthy. It gives incorrect result as well

You may refer to the attached photo. As you can see i update the formula point to "heathy" column. The Heathy Level text column still doesnt display the correct text 

[Ronald123]

Hello @kennylch ,

In the meassure you check the value [Net Qty].
You refer to the value 0.76 wich is the measure [Healthy].

Greets,

Ronald

[kennylch]

Hi. Thanks for point out. i have accidentally change the formula to netqty before send out. You may try to point to healthy. It gives incorrect result as well. 

You may refer to the attached photo. As you can see i update the formula point to "heathy" column. The Heathy Level text column still doesnt display the correct text