Measure with IF statement causes cross join

[Anonymous]

I have a table visual.
I've created a simple measure:  Total_participants=CALCULATE(MAX('FactTable'[NB_PARTICIPANTS]))
When adding this measure to the visual, the number of rows is correct aka one line per NO_REFERENCE.
However, when trying to create a new measure based on this measure using a IF statement:
KPI_participants=IF(CALCULATE(MAX('FactTable'[NB_PARTICIPANTS]))=0,0,1)
then suddenly there's is a cross join (cartesian product effect) aka I get tons of rows for the same NO_REFERENCE.

Any idea?
Thank you in advance!

Thank you.

[Anonymous]

Hi @Anonymous ,

Any updates?

How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

Best Regards,
Eyelyn Qin

[Anonymous]

Hi Evelyn,

Not yet. I've temporarily tried to create a calculated columns in my SQL statement but I really do not want to continue on that path.
I think the model design might cause the issue.
I have 3 tables.
DimDate
Fact_aggregated
Fact_detail


Relationships:  DimDate to Fact_detail (1 to many)
Fact_aggregated to Fact_detail (1 to many)  (key is reference_no)
The amount columns used to calculate my measures comes from Fact_aggregated.   Due to complexity of business rules and granularity issue, I couldn't juste summarize with everything in the Fact_detail. I had to have a separate Fact_aggregated.

So in a nutshell. I have 1 table visual with items coming from all 3 tables.  If I only used the amount columns from the Fact_Aggregated, I do not get cartesian product which is the correct result.  However, if I create a simple measure based on any of the same amount columns, I get a cartesian product.

@SpartaBI Yes, I'm available to demo the issue live.  Could you please let me know how to proceed?  Thank you.

[Anonymous]

I also forgot to mention that I had to enable BI-Directional cross filtering in all the relationships otherwise I would get cartesian product (even before adding a measure to the visual).

[SpartaBI]

@Anonymous Try:
MEASURE = 
VAR _result = CALCULATE(MAX('FactTable'[NB_PARTICIPANTS])
RETURN 
 (_result <> 0 ) * _result 

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

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 . 

[SpartaBI]

@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.

[tallison]

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.

[Anonymous]

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 you

[SpartaBI]

@Anonymous 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 messages  

[Anonymous]

I just tried this:
KPI Score =
VAR vResult=CALCULATE(MAX('FactTable'[NB_PARTICIPANTS]))
RETURN
SWITCH(TRUE(),
vResult=0,0,
vResult<50,1,2)
Still giving me cartesian product.

So to recap:
(_result <> 0 ) * _result  will give me one row but (_result <> 0 ) * 1 will give me multiple rows.
I need to give a score value that is different from the result itself.
Thanks for your help!

[SpartaBI]

@Anonymous I need to go offline for today. 
Can you please by tomorrow share a sample PBIX here with the problem and I'll try to solve and share the solution tomorrow.

[SpartaBI]

@Anonymous 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.

[SpartaBI]

@Anonymous also, please watch this great video:
https://www.youtube.com/watch?v=_cT9PB72fu8
maybe the clue to your solution is here 🙂