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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.