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    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'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.

6 Replies

  • 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?

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support
    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.