Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Column which gives common data

Hi All,

I have the following data. When in Matrix visual i want only the common data to show up i.e. only when there is data in the highlighted column. Condition is: If data is in Rel B and also in either Rel A or Rel C then the row should show up or else it should exclude it. 

Please help. Thanks a lot!

 

DateRel ARel BRel C
7/1/2021111
7/2/20211 1
7/3/20211  
7/4/2021 1 
7/5/20211 1
7/6/2021111
7/7/2021 1 
7/8/2021111
7/9/2021  1
7/10/2021  1
7/11/2021 1 
7/12/20211 1
7/13/2021 1 
7/14/2021111
7/15/2021 1 

 

Thanks

Richa

Data Protection
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi, Anonymous 

    I'm afraid it's difficult to highlight columns and then dynamiclly "show up" or "exclude" rows in the matrix.

    You may need to be implemented based on a slicer filter.

    Please try follow steps:
    1.add a new calculated table with new field "Rel Type" 

    Rel Type = DISTINCT('Original Table'[Rel Type])

    2.create a measure and apply it to visual filter pane

    Measure1 = 
IF (
    ISFILTERED ( 'Rel Type'[Rel Type] ),
    IF (
        SELECTEDVALUE ( 'Rel Type'[Rel Type] )
            = SELECTEDVALUE ( 'Rel Type'[Rel Type] )
            && CALCULATE (
                SUM ( 'Original Table'[Value] ),
                FILTER (
                    'Original Table',
                    'Original Table'[Rel Type] = SELECTEDVALUE ( 'Rel Type'[Rel Type] )
                )
            ) > 0
            && CALCULATE (
                SUM ( 'Original Table'[Value] ),
                FILTER (
                    'Original Table',
                    'Original Table'[Rel Type] <> SELECTEDVALUE ( 'Rel Type'[Rel Type] )
                )
            ) >= 1,
        1,
        0
    ),
    1
)

    Result:

    Please check my attached file for more details.

     

     

    Best Regards,
    Community Support Team _ Eason

1 Reply

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi, Anonymous 

    I'm afraid it's difficult to highlight columns and then dynamiclly "show up" or "exclude" rows in the matrix.

    You may need to be implemented based on a slicer filter.

    Please try follow steps:
    1.add a new calculated table with new field "Rel Type" 

    Rel Type = DISTINCT('Original Table'[Rel Type])

    2.create a measure and apply it to visual filter pane

    Measure1 = 
IF (
    ISFILTERED ( 'Rel Type'[Rel Type] ),
    IF (
        SELECTEDVALUE ( 'Rel Type'[Rel Type] )
            = SELECTEDVALUE ( 'Rel Type'[Rel Type] )
            && CALCULATE (
                SUM ( 'Original Table'[Value] ),
                FILTER (
                    'Original Table',
                    'Original Table'[Rel Type] = SELECTEDVALUE ( 'Rel Type'[Rel Type] )
                )
            ) > 0
            && CALCULATE (
                SUM ( 'Original Table'[Value] ),
                FILTER (
                    'Original Table',
                    'Original Table'[Rel Type] <> SELECTEDVALUE ( 'Rel Type'[Rel Type] )
                )
            ) >= 1,
        1,
        0
    ),
    1
)

    Result:

    Please check my attached file for more details.

     

     

    Best Regards,
    Community Support Team _ Eason