Forum Discussion
Comparing data in one column to another column in another table to return values to a new column
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?
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 [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
2 Replies
- SamInogic
Super User
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 [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
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.
