topic Re: Comparing two different columns based on a third column in DAX Commands and Tips

Comparing two different columns based on a third column

KipGum — Mon, 07 Nov 2022 17:21:05 GMT

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!

Re: Comparing two different columns based on a third column

tamerj1 — Mon, 07 Nov 2022 19:47:41 GMT

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

Re: Comparing two different columns based on a third column

KipGum — Mon, 07 Nov 2022 20:10:41 GMT

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?

Re: Comparing two different columns based on a third column

tamerj1 — Mon, 07 Nov 2022 20:15:43 GMT

KipGum

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

Re: Comparing two different columns based on a third column

KipGum — Mon, 07 Nov 2022 20:22:46 GMT

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

Re: Comparing two different columns based on a third column

KipGum — Mon, 07 Nov 2022 23:31:02 GMT

tamerj1

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

Re: Comparing two different columns based on a third column

v-jialluo-msft — Wed, 09 Nov 2022 06:47:50 GMT

^{Hi KipGum ,}

^{Please follow these steps:}

⁽¹⁾ ^{Create a new measure to select Level1}

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

⁽²⁾ ^{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" ) )

⁽³⁾ ^{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.}