Forum Discussion

Titatovenaar2's avatar
Titatovenaar2
Icon for Advocate II rankAdvocate II
3 years ago
Solved

DAX Measure: Show ID's where Column B has multiple values for Column A

Hi,   I have a simple table like this: ID Cat1 Cat2 1 A 01 2 A 02 3 B 01 4 C 01 5 D 01 6 D 01   I want to view the records where Cat1 has multiple values fo...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Expected outcome measure: =
VAR _result =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                DISTINCT ( 'Table'[Cat1] ),
                "@count",
                    CALCULATE (
                        COUNTROWS ( DISTINCT ( 'Table'[Cat2] ) ),
                        ALL ( 'Table'[ID], 'Table'[Cat2] )
                    )
            ),
            [@count] > 1
        )
    ) > 0
RETURN
    IF ( ISINSCOPE ( 'Table'[Cat1] ), DIVIDE ( _result, _result, 0 ) )
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

Please check the below picture and the attached pbix file.

 

 

 

Expected outcome measure: =
VAR _result =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                DISTINCT ( 'Table'[Cat1] ),
                "@count",
                    CALCULATE (
                        COUNTROWS ( DISTINCT ( 'Table'[Cat2] ) ),
                        ALL ( 'Table'[ID], 'Table'[Cat2] )
                    )
            ),
            [@count] > 1
        )
    ) > 0
RETURN
    IF ( ISINSCOPE ( 'Table'[Cat1] ), DIVIDE ( _result, _result, 0 ) )
  • Titatovenaar2's avatar
    Titatovenaar2
    Icon for Advocate II rankAdvocate II
    3 years ago

    Works like a charm! Nice way of using ISINSCOPE(). Thanks