Forum Discussion

Gianf's avatar
Gianf
Icon for Advocate I rankAdvocate I
1 year ago
Solved

Adding dimension(s) to matrix duplicates rows

Hi all, 

I have to calculate revenues in 4 different ways, based on as many flags in facts table; measures work OK and give expected result 

Flags are like this 

Now, if I merge together flags and corresponding measures, the matrix "explodes", showing also non-existent flags on the same row: 


The same happens if the users do the same using "Analyze in Excel" - which is the actual purpose of this semantic model. 

The desired output should be like this: 

Measures are built all the same way, like this 

each filtering the corresponding dimension and, as told before, they give the correct value, so I believe tha are not guilty. 
The weird thing is that, using simply the filter condition in Calculate(), instead of puttig it into a Filter() like I did, rows double again, so I ended with 16 rows each! 

Model is a regular star-schema: 


What am I missing? Can anyone help me resolving this issue? 

Thanks in advance, 

Gianfranco 

p. s. attached a sample file that reproduces the behavior
https://drive.google.com/file/d/1v3iunD9ZsfOyFjfcSHKC81KDISnDhX52/view?usp=sharing



Need Help

4 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    1 year ago

    Hi Gianf 

     

    I'm sure a DAX pro will come along and explain this in better terms.

    The Sales table can't filter the Compy tables. This leads to both rows being displayed.
    A quick and dirt fix is to change the relationships to Cross-Filter BOTH.

    This comes with dangers of its own:
    https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

    You could also create a measure for each to do this:

    Filter Compy CY D4D =
     CALCULATE(
        SELECTEDVALUE('Comp CY D4D'[Comparable])
        ,CROSSFILTER(
            'Comp CY D4D'[Comparable]
            ,Sales[ComparableCY_D4D]
            ,Both
        )
     )
     
    The added advantage being this only changes the relationship for that calculation and will hide the total and subtotal which provides nonsense at this point.

     

    • Gianf's avatar
      Gianf
      Icon for Advocate I rankAdvocate I
      1 year ago

      SamWiseOwl 

      Uhm... first of all I am trying the BOTH direction solution, but it does not seem to work: 

      The LY measures return empty except for the total value, unlike your screenshot 
      I would like to try the measure solution, but i am unclear  how to use it 

      Many thanks for your help!