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

     

     

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

  • pi_eye's avatar
    pi_eye
    Resolver IV
    3 years ago

    Hi Thor2022 

     

    This is interesting, because for some reason it's not as simple to aggregate a distinct count on another measure! However it is always possible in DAX 😉

     

    I created a new measure rather than chain it in called "New count" which evaluates to the total number of distinct sums. I am use group by to create an aggregated table which I can pass the summed column through to a  distinct count.

    New Count = calculate(
            countx(
                distinct(SELECTCOLUMNS( GROUPBY(WareHouse,WareHouse[WareHouse],"SumVal",sumx(CURRENTGROUP(),WareHouse[Value])),[SumVal]) ) ,
                [SumVal] ),
            ALLSELECTED(WareHouse[WareHouse])
    )
     
    On it's own, it looks like this: (sorry I can't seem to cut and paste images right now)
     12345678 
    WH142123333 
    WH242123333 
    WH342123333 
    WH442123333 
              
     

    Added into the final expression gives you: 

    Show diff Values = If([New Count]=1,"", Sum(WareHouse[Value]))

     

    Let me know if this works,

    Pi

    • Thor2022's avatar
      Thor2022
      Frequent Visitor
      3 years ago

      Hi Pi 

      I am sure its a great solution , but give me some time to digest it , since some of the used functions are new for me 🙂 

      Regards 

  • Thor2022's avatar
    Thor2022
    Frequent Visitor
    3 years ago

    Hi Paul 

    Thank you for your reply .

    I find the logic in your solution excellent but as I mentioned before, the Profit value is a measured value that can't be calculated in the average function !!! 
    looking forward to your reply 

    REgards 

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      3 years ago

      My apologies. I did see you mention it and then promptly forgot about it in the solution.

      This measure will work:

      No duplicate profits =
VAR _Profit = [Your measure]
VAR _Average =
    AVERAGEX ( ALL ( 'Warehouse table'[Warehouse] ), [Your measure] )
RETURN
    IF (
        AND (
            ISINSCOPE ( 'Warehouse table'[Warehouse] ),
            ISINSCOPE ( 'Year Table'[Year] )
        ),
        IF ( _Profit = _Average, BLANK (), _Profit )
    )
      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        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....