Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sebastienke
Helper III
Helper III

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.

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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]))

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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]))

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.