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

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

Reply
Irwin
Helper IV
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
114,312,2
125,511,4
135,311,7
213,912,1
22514,2
324,316,2
335,110,5
414,811,2
443,717,2

 

Table 2   
Distributor Product code Protein max Temperatur max
11513
12513
13613
21613
22513
32513
33612
41513
44414

 

Any help is appreciated 🙂

1 ACCEPTED SOLUTION

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 )

View solution in original post

4 REPLIES 4
johnt75
Super User
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 )

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

 

Irwin_0-1674816549797.png

 

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 )

That worked! Thank you so much! Appreciated 🙂

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.