How to use or replace CALCULATE when we have data in two different (but linked) tables ?

[sebastienke]

Hi, 

I have this measure which works perfectly. 

M: LxD_invoiced_month = CALCULATE(SUM(Joint_data[related amount]),Joint_data[To consider]=true())

Tables PNL (1) and Joint_Data (many) are linked with a cardinality (1:many).

In order to get the result that I wanted, I had to create the column related amount in Joint_Data  which is :

related amount = RELATED(PNL[Invoice_amount])

But I would like to take advantage of this cardinality and to not create a redundant column related amount and use directly Invoice_amount.

But the measures below don't work :

M: LxD_invoiced_month = CALCULATE(SUM(PNL[Invoice_amount]),Joint_data[To consider]=true())

M: LxD_invoiced_month = CALCULATE(SUM(RELATED(PNL[Invoice_amount])),Joint_data[To consider]=true())​

Someone to help me ?

Thank you.

[amitchandak · Accepted solution]

@sebastienke , Try like

M: LxD_invoiced_month = CALCULATE(SUM(PNL[Invoice_amount]),filter(Joint_data,Joint_data[To consider]))

or

M: LxD_invoiced_month = CALCULATE(SUMX(Joint_data,RELATED(PNL[Invoice_amount])),filter(Joint_data,Joint_data[To consider]))

[sebastienke]

so obvious..... 🤔
Thanks a lot @amitchandak