Forum Discussion
Measure with IF statement causes cross join
Thank you for your reply. What exactly are you trying to suggest me? I`ve done VAR/RETURN before but I'm not sure what you mean by (_result <> 0 ) * _result .
Anonymous
(_result <> 0 ) will return false if it's 0 and false is 0 so multiplying it by the result will give 0 and if it's true it's 1 so it will give the result.
The var and result stuff are just for best practice performance here, they are not the main thing here I want you to try.
Thank you,
I does work but I'm not sure why it works instead of the IF statement.
Also, if I have more than one condition such as
Something like:
IF _result =0 THEN 1 ELSE IF _result <50 THEN 2 ELSE 3
I'm separately trying the following:
(_result = 0 )*1
(_result <50 )*2
(_result >50 )*3
But even (_result = 0 )*1 alone doesn't work aka it gives me a cartesian product.
The goal of this KPI is to give a score (1,2 or 3) depending on a condition based on the original measure.
Thank youAnonymous try this pattern
MEASURE =
VAR _result = CALCULATE(MAX('FactTable'[NB_PARTICIPANTS])
RETURN
SWITCH(
TRUE,
(_result <> 0 ), _result,
..
..
)
Try to just return a blank if nothing is met.
Do it in the right order that it should be.
P.S. Will appreciate your kudos on my messagesAnonymous if you will not succeed please mention me here tomorrow, and I'll try to do a quick zoom with you together to see your model exactly.
Thank you so much!
I have been struggling with this. I have lots of cards and don't want them to display "(Blank)". Once I put an "if(isblank()..." on all of them everything blew up. This is exactly what I needed, and now I realize why I was getting what I thought were cartesian joins everywhere.
