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 ...
Need Help
  • 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).