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" ) )
KipGum's avatar
KipGum
Microsoft Employee
3 years ago

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's avatar
    tamerj1
    Community Champion
    3 years ago

    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's avatar
      KipGum
      Microsoft Employee
      3 years ago

      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's avatar
        KipGum
        Microsoft Employee
        3 years ago

        tamerj1 

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