Forum Discussion

HillHika's avatar
HillHika
Regular Visitor
2 years ago
Solved

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 t...
Need Help
  • SamInogic's avatar
    SamInogic
    2 years ago

    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/

SamInogic's avatar
SamInogic
Icon for Super User rankSuper User
2 years ago

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/

HillHika's avatar
HillHika
Regular Visitor
2 years ago

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.