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 toge...
Need Help
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!