What are the differences between duplicates of another column

[moshiera]

Here is an example of a test i'm doing... I would like to see visually what are the differences between column "Source".  I have no idea where to start.  See below for table example.  

I would like to see:

AD NOT IN SD

ComputerB, ComputerD, ComputerF

SD not in AD

ComputerX

 

[harshnathani · Accepted solution]

Hi @moshiera ,

You can create a Calculated Column,

Calculated_Value =

var __ADSD = EXCEPT(CALCULATETABLE(VALUES(Table4[Column1]),Table4[Column2] = "SD"),CALCULATETABLE(VALUES(Table4[Column1]),Table4[Column2] = "AD"))

var __SDAD = EXCEPT(CALCULATETABLE(VALUES(Table4[Column1]),Table4[Column2] = "AD"),CALCULATETABLE(VALUES(Table4[Column1]),Table4[Column2] = "SD"))

return

SWITCH(

true(),

Table4[Column1] IN __ADSD,"SD Not in AD",

Table4[Column1] IN __SDAD,"AD Not in SD",

""

)

Regards,

Harsh Nathani

do not hesitate to give a kudo to useful posts and mark solutions as solution

[v-kelly-msft · Accepted solution]

Hi @moshiera，

Create a measure as below:

Measure =
VAR _AD =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Computer Name ] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Source] = "AD" )
    )
VAR _SD =
    CALCULATETABLE (
        DISTINCT ( 'Table'[Computer Name ] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Source] = "SD" )
    )
RETURN
    IF (
        MAX ( 'Table'[Source] ) = "AD"
            && NOT ( MAX ( 'Table'[Computer Name ] ) IN _SD ),
        "AD not in SD",
        IF (
            MAX ( 'Table'[Source] ) = "SD"
                && NOT ( MAX ( 'Table'[Computer Name ] ) IN _AD ),
            "SD not in AD",
            BLANK ()
        )
    )

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[harshnathani · Accepted solution]

Hi @moshiera ,

You can create a Calculated Column,

Calculated_Value =

var __ADSD = EXCEPT(CALCULATETABLE(VALUES(Table4[Column1]),Table4[Column2] = "SD"),CALCULATETABLE(VALUES(Table4[Column1]),Table4[Column2] = "AD"))

var __SDAD = EXCEPT(CALCULATETABLE(VALUES(Table4[Column1]),Table4[Column2] = "AD"),CALCULATETABLE(VALUES(Table4[Column1]),Table4[Column2] = "SD"))

return

SWITCH(

true(),

Table4[Column1] IN __ADSD,"SD Not in AD",

Table4[Column1] IN __SDAD,"AD Not in SD",

""

)

Regards,

Harsh Nathani

do not hesitate to give a kudo to useful posts and mark solutions as solution