cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sime
Frequent Visitor

Table Value Comparison

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.

 

1 ACCEPTED SOLUTION
Samarth_18
Super User
Super User

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

 


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


View solution in original post

4 REPLIES 4
Samarth_18
Super User
Super User

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

 


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


Sime
Frequent Visitor

Thank you very much for your assistance @Samarth_18 .  This worked as anticipated. 

freginier
Responsive Resident
Responsive Resident

Sime
Frequent Visitor

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.

 

Sime_0-1642705391229.png

 

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!