cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper IV

## Compare value from a columns to another column with conditions?

Hi all,

I am sorry if the title is confusing. I am not entirely sure how to correctly label my question.

I have two tables. I want to compare a value from the first table (ie. temperature) to the "max value" from the other table.

Here I need to also consider that there is different max values related to distributor/product code.

I would like the output to be.

"IF(Temperature from table one > max temperature from table two then 1 eller 0)"

However I need the measure to somehow check

table ones distributor and product code to match table two as there are different max values.

 Table 1 Distributor Product code Protein Temperature 1 1 4,3 12,2 1 2 5,5 11,4 1 3 5,3 11,7 2 1 3,9 12,1 2 2 5 14,2 3 2 4,3 16,2 3 3 5,1 10,5 4 1 4,8 11,2 4 4 3,7 17,2

 Table 2 Distributor Product code Protein max Temperatur max 1 1 5 13 1 2 5 13 1 3 6 13 2 1 6 13 2 2 5 13 3 2 5 13 3 3 6 12 4 1 5 13 4 4 4 14

Any help is appreciated 🙂

1 ACCEPTED SOLUTION
Super User

It shouldn't be using SELECTEDVALUE as it is a column not a measure, that's my mistake.

``````Temperature comparison =
VAR ProductCode =
'Table1'[ Product code]
VAR Distributor =
'Table1'[Distributor]
VAR Temperature =
'Table1'[ Temperature]
VAR T2Temp =
CALCULATE (
MAX ( 'Table2'[ Temperatur max] ),
TREATAS (
{ ( ProductCode, Distributor ) },
'Table2'[ Product code],
'Table2'[Distributor]
)
)
RETURN
IF ( Temperature > T2Temp, 1, 0 )``````
4 REPLIES 4
Super User

Try

``````Temperature comparison =
VAR ProductCode =
SELECTEDVALUE ( 'Table1'[Product code] )
VAR Distributor =
SELECTEDVALUE ( 'Table1'[Distributor] )
VAR Temperature =
SELECTEDVALUE ( 'Table1'[Temperature] )
VAR T2Temp =
CALCULATE (
MAX ( 'Table2'[Temperature] ),
TREATAS (
{ ( ProductCode, Distributor ) },
'Table2'[Product code],
'Table2'[Distributor]
)
)
RETURN
IF ( Temperature > T2Temp, 1, 0 )
``````
Helper IV

Thank you so much for your reply! I think you might be on to something, but unfortunately this just gives "0" for all values.

Super User

It shouldn't be using SELECTEDVALUE as it is a column not a measure, that's my mistake.

``````Temperature comparison =
VAR ProductCode =
'Table1'[ Product code]
VAR Distributor =
'Table1'[Distributor]
VAR Temperature =
'Table1'[ Temperature]
VAR T2Temp =
CALCULATE (
MAX ( 'Table2'[ Temperatur max] ),
TREATAS (
{ ( ProductCode, Distributor ) },
'Table2'[ Product code],
'Table2'[Distributor]
)
)
RETURN
IF ( Temperature > T2Temp, 1, 0 )``````
Helper IV

That worked! Thank you so much! Appreciated 🙂

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors