How to dertermine the latest row in a related table?

[mcobelens]

Hi There,

I'm struggling with the following:

I have Clients, Retailers, Invoices and Switchers

Retailers have main clients and the table Switchers contains the date that a retailers main client changed/switched.

My question is, is it possible to create a measure in retailers that determines the main client by month.
I need to create a view where I need to know what the main client was for a retailer, but the table switchers only contains data for the months that the switch happened.

sample pibx

[v-shex-msft]

HI @mcobelens,

Maybe you can try to use USERELATIONSHIP function to use inactive relationship to calculate:

USERELATIONSHIP in Calculated Columns

Regards,

Xiaoxin Sheng

[mcobelens]

Thanks for your suggestion but I'm affraid that I don't quite understand how that would work.

[v-shex-msft]

HI @mcobelens,

It is similar as a normal calculate filter condition, you can defined USERELATIONSHIP in calculate function, then your formula can calculate based on specific relationship which defined even if it not activated.

Regards,

Xiaoxin Sheng