Forum Discussion

Thor2022's avatar
Thor2022
Frequent Visitor
3 years ago
Solved

Filter the contents inside MATRIX visual

Dear All 

I have a matric that contains the following information: 

- Rows: Warehouse names: A, B and C 

- Columns: Years 

- Values: Profits 

 

and I have a multiselection slicer that contains the Warehouse names. 

 

I want to be able to compare the chosen values in the slicer and see the result in the Matrix in a way that the MATRIX will only show comparison if they are different, but if they are identical it will show blank, this is an example 

 

Thanks in advance 

Regards 

 

 

Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    Thor2022 

    Actually, thinking about it, the average method could potentially not achieve what you are looking for.

    If you have a set of values for a year like {30, 29, 30, 31}, the average method will hide both 30 values.

    The following  method however will work: compare the minimum value in a set to the maximum value. If they are the same, all values are the same...

    So...

     

    No duplicate profits =
VAR _Min =
    MINX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] )
VAR _MAX =
    MAXX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] )
RETURN
    IF (
        AND (
            ISINSCOPE ( 'Warehouse table'[Warehouse] ),
            ISINSCOPE ( 'Year Table'[Year] )
        ),
        IF ( _Min = _Max, BLANK (), [Your measure] )
    )

     

     

    PS. I tried deleting the previous message but the forum won't let me for that particular post for some reason....

     

     

10 Replies

  • pi_eye's avatar
    pi_eye
    Resolver IV
    3 years ago

    Hi Thor

     

    Can you elaborate on the example? So for example if the column values for year #2 are 5,6 and 7, it shows but if it is 3,3, and 3  it is blank?

     

    See example below:

     

    Try this expresion in DAX:

    Show diff Values = If(Calculate(DISTINCTCOUNT(WareHouse[Value]),ALLSELECTED(WareHouse[WareHouse]))=1,"", Sum(WareHouse[Value]))

     

    What it is doing:

    Calculate(DISTINCTCOUNT(WareHouse[Value]),ALLSELECTED(WareHouse[WareHouse])) --> Gives you the total of distinct values per warehouse

    If(....... =1,"",...) ---> tests for the distinct values =1, thie means they are all the same. Returns blank if that is the case, and the sum if they are different.

     

     

     

    Thanks,

     

    Pi

    • Thor2022's avatar
      Thor2022
      Frequent Visitor
      3 years ago

      Hi Pi

      Thanks a lot, I will try it on Monday. 

      Until then, I just need to add that the profit value is a measure, not a column .. so I am not sure if this DAX can be applied? 

      Thanks a gain 

      Regards 

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        3 years ago

        Here is one way. Basically you compare the sum (profit) of each warehouse with the average for all warehouses in a year.

        First the model:

         The calculation (without totals)

         

        No duplicate profits =
VAR _Profit =
    SUM ( fTable[Profit] )
VAR _Average =
    CALCULATE ( AVERAGE ( fTable[Profit] ), ALL ( 'Warehouse table'[Warehouse] ) )
RETURN
    IF (
        AND (
            ISINSCOPE ( 'Warehouse table'[Warehouse] ),
            ISINSCOPE ( 'Year Table'[Year] )
        ),
        IF ( _Profit = _Average, BLANK (), _Profit )
    )

         

        and if you need totals:

         

        No Dups with totals = 
SUMX(SUMMARIZE(fTable, 'Warehouse table'[Warehouse], 'Year Table'[Year]), [No duplicate profits])

         

         

        Sample PBIX attached