Forum Discussion

KipGum's avatar
KipGum
Microsoft Employee
3 years ago
Solved

Comparing two different columns based on a third column

Hi all,   I have the following table:   UpdateLevel1     DomainAndSam    AffectedObject   Level1                 contoso\User1       contoso\Group1 Level1                 contoso\Group9    con...
  • v-jialluo-msft's avatar
    v-jialluo-msft
    3 years ago

    Hi KipGum ,

     

    Please follow these steps:

    (1) Create a new measure to select Level1

     

    UpdateLevel1 = 
IF (
    ISFILTERED ( 'PrivilegedAccounts'[DomainAndSam] ),
    IF (
        MAX( 'PrivilegedAccounts2'[DomainAndSam] )
            IN VALUES ( PrivilegedAccounts[DomainAndSam] ),
        "Level1"
    )
)

     

    (2) Create a new measure to select Level2

     

    UpdateLevel2 =
VAR _in =
    SUMMARIZE (
        FILTER (
            ALL ( 'PrivilegedAccounts2' ),
            [DomainAndSam] IN VALUES ( PrivilegedAccounts[DomainAndSam] )
        ),
        [AffectedObject]
    )
RETURN
    IF (
        ISFILTERED ( 'PrivilegedAccounts'[DomainAndSam] ),
        IF ( MAX ( 'PrivilegedAccounts2'[DomainAndSam] ) IN _in, "Level 2" )
    )

     


    (3)     The end result

     

     

    Best Regards,

    Gallen Luo

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

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi KipGum 
Please try

UpdateLevel2 =
VAR CurrentLevel1 = [UpdateLevel1]
VAR CurrentDomain =
    SELECTEDVALUE ( 'Table'[DomainAndSam] )
VAR T1 =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[DomainAndSam], 'Table'[AffectedObject] ),
        ALLSELECTED ( 'Table' )
    )
VAR T2 =
    ADDCOLUMNS (
        T1,
        "@Level1",
            VAR DomainSam = 'Table'[DomainAndSam]
            VAR AffectedObject = 'Table'[AffectedObject]
            RETURN
                CALCULATE (
                    [UpdateLevel1],
                    ALL ( 'Table' ),
                    'Table'[DomainAndSam] = Domain,
                    'Table'[AffectedObject] = AffectedObject
                )
    )
VAR T3 =
    FILTER ( T2, [@Level1] <> BLANK () )
VAR T4 =
    SELECTCOLUMNS ( T3, "@Object", [AffectedObject] )
RETURN
    IF ( ISBLANK ( CurrentLevel1 ), IF ( CurrentDomain IN T4, "Level2" ) )