Comparing two different columns based on a third column

[KipGum]

Hi all,

I have the following table:

UpdateLevel1     DomainAndSam    AffectedObject

Level1                 contoso\User1       contoso\Group1

Level1                 contoso\Group9    contoso\Group10

<Blank>             contoso\User2        contoso\Group2

<Blank>             contoso\Group1     contoso\Group3

<Blank>             contoso\Group10   contoso\Group11

I need to create a new column called UpdateLevel2 = "Level2" if the DomainAndSam column matches the AffectedObject of any of the records that have UpdateLevel1="Level1".  From the example above, the output should look like this:

UpdateLevel1     DomainAndSam    AffectedObject        UpdateLevel2

Level1                 contoso\User1       contoso\Group1       <Blank>

Level1                 contoso\Group9    contoso\Group10     <Blank>

<Blank>             contoso\User2        contoso\Group2      <Blank>

<Blank>             contoso\Group1     contoso\Group3       Level2

<Blank>             contoso\Group10   contoso\Group11     Level2

The UpdateLevel1 column is calculated as a measure.

Any help would be greatly appreciated!

[v-jialluo-msft · Accepted solution]

^{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]

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" ) )

[KipGum]

I just tested this but it's returning only blanks.  My table name is "PrivilegedAccounts2".  I'm guessing that the line that reads:

'Table'[DomainAndSam] = Domain,

 was meant to read:

'Table'[DomainAndSam] = DomainSam,

Here's the code as I am testing it:

UpdateLevel2 =

VAR CurrentLevel1 = [UpdateLevel1]

VAR CurrentDomain =

    SELECTEDVALUE ( 'PrivilegedAccounts2'[DomainAndSam] )

VAR T1 =

    CALCULATETABLE (

        SUMMARIZE ( 'PrivilegedAccounts2', 'PrivilegedAccounts2'[DomainAndSam], 'PrivilegedAccounts2'[AffectedObject] ),

        ALLSELECTED ( 'PrivilegedAccounts2' )

    )

VAR T2 =

    ADDCOLUMNS (

        T1,

        "@Level1",

            VAR DomainSam = 'PrivilegedAccounts2'[DomainAndSam]

            VAR AffectedObject = 'PrivilegedAccounts2'[AffectedObject]

            RETURN

                CALCULATE (

                    [UpdateLevel1],

                    ALL ( 'PrivilegedAccounts2' ),

                    'PrivilegedAccounts2'[DomainAndSam] = DomainSam,

                    'PrivilegedAccounts2'[AffectedObject] = AffectedObject

                )

    )

VAR T3 =

    FILTER ( T2, [@Level1] <> BLANK () )

VAR T4 =

    SELECTCOLUMNS ( T3, "@Object", [AffectedObject] )

RETURN

     IF ( ISBLANK ( CurrentLevel1 ), IF ( CurrentDomain IN T4, "Level2" ) )

Have any ideas?

[tamerj1]

@KipGum 

What do you have in the table visual? Any other table involved? I assumed only UpdateLevel1 is measure, the other two are columns?

[KipGum]

I have another table called "PrivilegedAccounts" which has the exact same columns as "PrivlegedAccounts2".  I have a slicer for DomainAndSam on "PrivilegedAccounts".  I have the following Measure in "PrivilegedAccounts2" that populates "UpdateLevel1" with the selected DomainAndSam value from "PrivilegeAccounts":

UpdateLevel1 = VAR sele = SELECTEDVALUE(PrivilegedAccounts[DomainAndSam]) RETURN IF(ISFILTERED(PrivilegedAccounts[DomainAndSam]) && MAX(PrivilegedAccounts2[DomainAndSam]) = sele,"Level1","")

[KipGum]

@tamerj1 

Do you think there is a solution to this?  Or do you think my use of UpdateLevel1 is flawed?