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

     

     

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

 

 

  • Thor2022's avatar
    Thor2022
    Frequent Visitor
    3 years ago

    Great work 

    Highly appricated