Comparing data in one column to another column in another table to return values to a new column

[HillHika]

I have a list of customer ids in one column in a table and if they appear in another column in a separate table I want to return "CPT" and if they don't appear I want to return "NonCPT"

I tried the following, but it just brings up CPT for every row and if I change the 1 to a 0 it comes up with NonCPT for every entry. Getting rid of the number completely just brings back errors. NOTE: There are thousands of entries and only 123 of them should come up with CPT and the rest should be NonCPT.

Thoughts?

VAR RelatedRows =

    CALCULATE (

        COUNTROWS ( RELATEDTABLE('CPTs A-') ),

        CROSSFILTER ( 'Program Attendee Report'[ACID], 'CPTs A-'[CPT A-], Both )

    )

RETURN IF ( 1, "CPT", "Non CPT" )

[SamInogic · Accepted solution]

Hi @HillHika 

You could try below DAX for this,

VAR RelatedRows =
CALCULATE (
COUNTROWS ( RELATEDTABLE('CPTs A-') ),
CROSSFILTER ( 'Program Attendee Report'[ACID], 'CPTs A-'[CPT A-], Both )
)
RETURN IF ( RelatedRows > 0, "CPT", "Non CPT" )

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[HillHika]

Thanks! It's counting them up correctly, which I don't understand because some numbers greater than zero aren't in both lists, and the numbers for comparison will continue to grow each week, but seems to be working. Much appreciated.