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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors