topic Re: Compare master column to two other columns in DAX Commands and Tips

Compare master column to two other columns

Anonymous — Mon, 24 Jan 2022 20:01:59 GMT

I haven't quite found my exact issue in an existing forum post; if there's something already out there on this topic I'd be happy to take a look.

I have a table that contains a list of Contact IDs - each numerical ID corresponds to a person. I have two other tables with active relationships to this table - one is a table that has a list of Contact IDs for competitors, and one that is a table that has a list of Contact IDs for alumni.

In my All Contacts table, I have created two calculated columns (CompetitorSwitch and AlumniSwitch) to identify the status of each contact for these two categories:

What I'm trying to do here is create a new column (or measure if that makes more sense) that tells me when a contact is BOTH a competitor and an alumni. I've tried IF statements, SWITCH/TRUE() statements, calculated tables...it's all failed me. I'm not sure if it's a mistake at this stage, or if I messed up by creating the two SWITCH columns first. You'll notice in the data model screenshot that the Alumni and Competitor tables do not have an active relationship, ruling out RELATED and similar functions.

I'm completely stuck...open to ideas, and can add more info on the data model if needed. Thanks in advance!

Re: Compare master column to two other columns

ValtteriN — Mon, 24 Jan 2022 20:32:12 GMT

Hi,

Might I inquire how your IF and SWITCH tries failed? The basic formula for two IF conditions is as follows:

True Temperature = IF(AND(Temperature[Temperature]="Cold",Temperature[Temp2]="Cold"),"Very Cold",Temperature[Temp2])

Example data:

If this pattern doesn't work ping me with @

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Compare master column to two other columns

Anonymous — Mon, 24 Jan 2022 21:50:46 GMT

ValtteriNI tried your method and here is what it gave me:

That's the code...here is the full error message:

Here is what my calculated columns look like (both CompetitorSwitch and AlumniSwitch are set up the same):

Re: Compare master column to two other columns

ValtteriN — Tue, 25 Jan 2022 06:39:24 GMT

Hi,

This error can usually be resolved by using MAX() arounf your columns. E.g.

True Temperature = IF(AND(max(Temperature[Temperature])="Cold",max(Temperature[Temp2])="Cold"),"Very Cold",Temperature[Temp2])

Re: Compare master column to two other columns

Anonymous — Tue, 25 Jan 2022 21:01:52 GMT

ValtteriN Unfortunately this only gives me the ELSE result:

Here is my calculated column now:

Re: Compare master column to two other columns

Anonymous — Tue, 25 Jan 2022 21:09:20 GMT

ValtteriN It turns out I was getting too cute with my DAX code. Solved my problem with this:

Now it's working as it should:

Thank you for the time and insight!