Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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.

 

 

  • Anonymous , 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]))

2 Replies

  • Anonymous , 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]))