Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Measure with IF statement causes cross join

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.

Need Help

13 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        SpartaBI
        Community Champion
        4 years ago

        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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

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