Need Help With Dax! Calculated Column 1 to many. If text contains..

[Bpark1994]

Hi,

Say i have 2 tables joined with a 1 to many relationship.

TableA

ID

1

2

3

TableB

ID | Status

1 | Rejected

1 | Approved

2 | Pending

2 | Invoiced

3 | Sent

3 | Pending

3 | Invoiced

I want to add a calculated column to TableA that checks to see if any of the corresponding IDs are invoiced and if so put a yes, if not put a no.

End result should look like the following

TableA

ID | Contains Invoiced?

1 | No

2 | Yes

3 | Yes

Thanks for any help!

[Anonymous]

This may not be the best approach - but I would create a new calculated column on Table B of "isInvoiced" where the dax would look something like this - IF(Status="Invoiced","Yes","No") - then do a calcualted measure "Contains Invoiced" which is the MAX(isInvoiced) of this new column - "Yes" is above "No" in text,  so then row context should kick in on putting ID with this new Calculated Measure and show Yes if there is an Invoiced on it, and No if there is not.

[Bpark1994]

Hi st3vemartin,

Unfortuanently when i do that and add the measure as a calculated column into TableA every row is just populated with "Yes" whether it is suppose to be or not.

[Zubair_Muhammad · Accepted solution]

@Bpark1994

Try this one

Column =
CONTAINS ( RelatedTable(TableB), [Status], "Invoiced" )

[Bpark1994]

You're my hero