Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I would like to compare the values of a column (same data) from 2 different tables and flag the unique values in a conditional column in the dataset so they can be filtered out.
Basically this is an example of what I am looking to do.
Table1.ColumnProject
12345
12346
12347
12348
Table2.ColumnProject
12345
12346
12347
12348
12349
12350
So the unique values in Table 2 (12349 & 12350) would be flagged as "New Projects" in a conditional column, and the reset would be "Existing Projects"
Can this be easily done?
Thanks.
Solved! Go to Solution.
Hi @Sime ,
You can create a custom column in table 2 with below code:-
column =
VAR result =
CALCULATE (
FIRSTNONBLANK ( '__Table 1'[ColumnProject], 1 ),
FILTER (
ALL ( '__Table 1' ),
'__Table 1'[ColumnProject] = '__Table 2'[ColumnProject]
)
)
RETURN
IF ( ISBLANK ( result ), "Existing Project", "new project" )
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @Sime ,
You can create a custom column in table 2 with below code:-
column =
VAR result =
CALCULATE (
FIRSTNONBLANK ( '__Table 1'[ColumnProject], 1 ),
FILTER (
ALL ( '__Table 1' ),
'__Table 1'[ColumnProject] = '__Table 2'[ColumnProject]
)
)
RETURN
IF ( ISBLANK ( result ), "Existing Project", "new project" )
Thanks,
Samarth
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Thank you very much for your assistance @Samarth_18 . This worked as anticipated.
Hi
it's very similare to this post : https://community.powerbi.com/t5/Desktop/IP-Address-Compare-and-Categorize/m-p/2282510#M827807
Hi @freginier,
Thank you for your response. I am unable to get the solution you have posted above to work in my scenario as the 3rd argument appears to not accept the second table and [column] when creating the measure.
Any ideas where I am going wrong?
Ideally if I could perform this cross table comparison in the data set and add a calulated column that would be ideal.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |