Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create a matrix that each row use different filter

Hi,

I'm trying to create a matrix table that needs to have a different filter for each segment of the matrix.

For example,

 

A, B, C, D are from the same column in a reference table and they are also using the same column as value, but the matrix we need to create requires each of A, B, C, D to use a different filter. Is there any possible solution to have this matrix created?

 

 JanFebMarch
A1   
A2   
A3   
A Sub Total   
B1   
B2   
B3   
B Sub Total   
C1   
C2   
C3   
C Sub Total   
D1   
D2   
D3   
D Sub Total   
Grand Total   

 

Thank you in advance.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    1.My sample data is this.

    Category

    Sub

    Month

    Value

    A

    A1

    Jan

    1

    A

    A2

    Feb

    2

    A

    A3

    Mar

    3

    B

    B1

    Jan

    4

    B

    B2

    Feb

    5

    B

    B3

    Mar

    6

    C

    C1

    Jan

    7

    C

    C2

    Feb

    8

    C

    C3

    Mar

    9

    D

    D1

    Jan

    1

    D

    D2

    Feb

    2

    D

    D3

    Mar

    3

     

     

    2.Create four new tables by entering data. There is no relationship among five tables.

     

    3.Create a measure.

    Values = 
IF (
    ISFILTERED ( A[Sub] )
        && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'A'[Sub] ),
    SUM ( 'Table'[Value] ),
    IF (
        ISFILTERED ( B[Sub] )
            && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'B'[Sub] ),
        SUM ( 'Table'[Value] ),
        IF (
            ISFILTERED ( C[Sub] )
                && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'C'[Sub] ),
            SUM ( 'Table'[Value] ),
            IF (
                ISFILTERED ( D[Sub] )
                    && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'D'[Sub] ),
                SUM ( 'Table'[Value] )
            )
        )
    )
)

     

    4.The result is as follows.

     

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , not very clear. Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

     

    Now you can check the value in measure and change calculation or filter example

     

    New measure  =

    Switch( True(),

    Max(Table[Categorty]) ="A" , [Measure],

    Max(Table[Categorty]) ="B" , [Measure1],

    [Measure3]

    )

     

    Refer by HR blog where I moved filter-based  is filtered

    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

     

    isinscope - change calculation based on level

    https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    1.My sample data is this.

    Category

    Sub

    Month

    Value

    A

    A1

    Jan

    1

    A

    A2

    Feb

    2

    A

    A3

    Mar

    3

    B

    B1

    Jan

    4

    B

    B2

    Feb

    5

    B

    B3

    Mar

    6

    C

    C1

    Jan

    7

    C

    C2

    Feb

    8

    C

    C3

    Mar

    9

    D

    D1

    Jan

    1

    D

    D2

    Feb

    2

    D

    D3

    Mar

    3

     

     

    2.Create four new tables by entering data. There is no relationship among five tables.

     

    3.Create a measure.

    Values = 
IF (
    ISFILTERED ( A[Sub] )
        && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'A'[Sub] ),
    SUM ( 'Table'[Value] ),
    IF (
        ISFILTERED ( B[Sub] )
            && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'B'[Sub] ),
        SUM ( 'Table'[Value] ),
        IF (
            ISFILTERED ( C[Sub] )
                && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'C'[Sub] ),
            SUM ( 'Table'[Value] ),
            IF (
                ISFILTERED ( D[Sub] )
                    && MAX ( 'Table'[Sub] ) IN ALLSELECTED ( 'D'[Sub] ),
                SUM ( 'Table'[Value] )
            )
        )
    )
)

     

    4.The result is as follows.

     

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.