Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kennedy311
Advocate I
Advocate I

Compare master column to two other columns

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.

 

kennedy311_0-1643054184081.png

 

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:

kennedy311_1-1643054281501.png

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!

 

 

 

 

1 ACCEPTED SOLUTION

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

 

kennedy311_0-1643144912892.png

 

Now it's working as it should:

 

kennedy311_1-1643144950679.png

 

Thank you for the time and insight!

View solution in original post

5 REPLIES 5
kennedy311
Advocate I
Advocate I

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

kennedy311_0-1643060940176.png

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

kennedy311_1-1643060961874.png

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

kennedy311_2-1643061003291.png

 

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])
ValtteriN_0-1643092757685.png

 







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@ValtteriN  Unfortunately this only gives me the ELSE result:

 

kennedy311_0-1643144439080.png

 

Here is my calculated column now:

 

kennedy311_1-1643144496797.png

 

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

 

kennedy311_0-1643144912892.png

 

Now it's working as it should:

 

kennedy311_1-1643144950679.png

 

Thank you for the time and insight!

ValtteriN
Super User
Super User

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:
ValtteriN_0-1643056280116.png

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!


 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors