PowerBi IF Filter based on value in column from different table

Question

I have 2 tables as below Table1

 Value     Status
    A      Normal
    B      InProgress
    C      InProgress

Table2

Value   SLA
A      10:00
B      13:00 
C      Multiple Times a Day

I need to create a measure to return numbers based on status and Time values from column - SLA of Table2. So, my conditions are:

1. If Status is "Normal" return 1
2. If Status is "InProgress", check corresponding 'SLA' value in Table2 and see if SLA is greater than current time or less than current time.
If its greater return 2, if its less return 3.
3. If SLA value is 'Multiple Times a Day' return 4.

How can I do this please? Thanks for help

And what is your question? What have you tried so far, where are you stuck? — Nico Haase
I have tried creating a measure for same as below : Status_Mod = if ( SELECTEDVALUE(Table1[STATUS])="Normal", 1, if (SELECTEDVALUE(Table1[STATUS])="InProgress/ Due Later" && FILTER(Table1, RELATED(..)))) Stuck at above. Not getting option to pick column - SLA from Table2. I have already created many to many relationship between both tables. — Bhrigu Malhotra

AnkUser AnkUser · Accepted Answer · 2019-08-20T13:03:31

It depends what your two table has relationship.

When I simply had 1:1 from Table 1 to Table2 I could easily get desired result based on your condition.

Below Calculated field expression based on 1:1 Relationship. You might want to change your return value and small condition as I had Value from Table 2 as text.

  Return = IF('Table'[Status]="Normal";1;
IF('Table'[Status]="InProgress";IF(LOOKUPVALUE('Table (2)'[SLA];'Table (2)'[Value];'Table'[Value])>"10:00";2;2);IF(LOOKUPVALUE('Table (2)'[SLA];'Table (2)'[Value];'Table'[Value])="Multiple Times a Day";4;0))
)

Now when you change relationship from 1:1 to 1:M or M:M simple LOOKUPVALUE will not help as DAX will return many rows for 1 Primary Key.

Here you will need to use CALCUALTE or similar funciton depending on your actual data. Now for simplyfication I created new Colum Return and gave value to it

Return = If('Table (3)'[SLA]="10:00";2;IF('Table (3)'[SLA]="Multiple Times a Day";4;3))

NOW Based on Return column I am using below function to return final result

Column = IF('Table'[Status]="Normal";1;CALCULATE ( 
Sum('Table (2)'[Return]);
FILTER(
    ALL('Table (2)');
    'Table (2)'[Value]='Table'[Value]
    )
    )
    )

PowerBi IF Filter based on value in column from different table

1 Answers