Get ID existing in another table based on a condition in Power Bi

[Raj12]

There are 2 tables,
Table 1 has ID &Value 

Table 2 has ID & Status

Need new column in Table 1 with matching ID's in Table 2, if any match has "L" then o/p will be 1 else 0

Table 1
ID	Val
as	111
ad	222
aa	333

Table 2
ID	Status
as	L
as	D
ad	L
ad	L
aa	D

Output Table
ID	Val	Output
as	111	1
ad	222	1
aa	333	0

Tries using 

IF ( 'Table1'[ID] IN DISTINCT ( Table2[ID]), 1, 0 ) but it doesn't include any confition of status in Table 2

[v-nuoc-msft · Accepted solution]

Hi @Raj12 

For your question, here is the method I provided:

Here's some dummy data

"Table 1"

"Table 2"

Create a measure.

Output = 
VAR _ID = SELECTEDVALUE('Table 1'[ID])
VAR _Status = CALCULATE ( MAX ('Table 2'[Status]), FILTER ('Table 2', 'Table 2'[ID] = _ID ) )
RETURN
SWITCH (
    _Status,
    "L", 1,
    0
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

In Table 1, enter this calculated column formula

Output = if(calculate(countrows('Table 2'),filter('Table 2','Table 2'[ID]=earlier('Table 1'[ID])&&'Table 2'[Status]="L"))>0,1,0)

Hope this helps.

[v-nuoc-msft · Accepted solution]

Hi @Raj12 

For your question, here is the method I provided:

Here's some dummy data

"Table 1"

"Table 2"

Create a measure.

Output = 
VAR _ID = SELECTEDVALUE('Table 1'[ID])
VAR _Status = CALCULATE ( MAX ('Table 2'[Status]), FILTER ('Table 2', 'Table 2'[ID] = _ID ) )
RETURN
SWITCH (
    _Status,
    "L", 1,
    0
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,
you can take a look at this one; maybe it works. I'm kind of new to DAX, and this function feels like a workaround.

Test = 
IF (
    // IF statement checks a condition
    CALCULATE(
        // CALCULATE evaluates an expression in a modified context
        COUNTROWS('Tabelle 2'), // COUNTROWS counts the number of rows in a table or table expression
        FILTER(
            // FILTER applies filters to a table or table expression
            'Tabelle 2', // Table being filtered
            'Tabelle 2'[ID] = 'Tabelle 1'[ID] && 'Tabelle 2'[Status] = "L"
            // Condition for filtering: Matches ID between Table1 and Table2, and Status is "L"
        )
    ) > 0, // Check if the count of filtered rows is greater than 0
    1, // If true, assign 1
    0 // If false, assign 0
)