IF measures on text fields

[KatieH]

Hi - looking for some more help!

I have 2 tables linked in a many to one relationship.

The first is a master tableshowing all sites.

The second is a table showing site orders for the previous year.

I have a field in both tables showing the "site status". The second table has the most up to date information regarding site information but it only shows orders that have ordered in the last 12 months.

I am building a table/matrix that shows all the sites and would like it to show the site status from table 2 unless that is blank in which case it should show the site status from table 1. Am I missing something really obvious here as I can't figure out how to do it!

Thank you! 

[v-yuezhe-msft]

@KatieH,

Do you get expected result after using smoupre's DAX? If not, please share dummy data of the two tables for us to analyze.

Regards,
Lydia

[Greg_Deckler]

So which way is the many to one?

table1 1-* table2

or

table1 *-1 table1

[KatieH]

Hi @Greg_Deckler,

it's the first one:

table1 1-* table2

Thanks,

[Greg_Deckler · Accepted solution]

Measure10 = IF(ISBLANK(MAX([Value])),LOOKUPVALUE(IDValues[Value],IDValues[Date],MAX(IDValues[Date])),MAX([Value]))

[KatieH]

Thanks @Greg_Deckler,

I couldn't quite get the lookupvalue to work but I got round it using the MAX calculations.