cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Keano
Frequent Visitor

Struggling with Scenario for Related Table and Filters

I'm relatively new to Power BI, and struggling a bit to get data from one table filtered into another table, just not sure what way to do it using DAX , would be comfortable enough on how to do it in SQL but in this case I can't as the data from 1 of the tables must come in the form of an excel sheet that gets uploaded to PBI.

 

I have a REVENUE table which contains data on a transactional level for every Invoice produced, on a simple level it has columns for 'Invoice Number', 'Invoice Date', 'Item' and 'Sales Value'

 

Also I have a COST table, this comes from Excel, the item cost changes only every month (It's a complex calculation so maintained and updated in excel) , so this table has columns for 'Date', 'Item' and 'Cost'.

 

The REVENUE table and COSTS table are not linked together ( as they would be many to many), they link through a common  ITEMS table where there is 1 record per item.

 

I want to bring the 'Cost' from the COSTS table into the REVENUE table and create a measure\column where the REVENUE table looks into the COSTS table and if the REVENUE 'Invoice date' matches the COST 'Date' and the REVENUE 'item' matches the COST 'Item' then pull in the COSTS 'cost' field

 

I suspect I have to use REALTEDTABLE function and some FILTER FUNCTIONS to do this but struggling to get the formulas correct, appreciate any guidance

1 REPLY 1
v-frfei-msft
Community Support
Community Support

Hi @Keano,

 

Could you please share your sample data and excepted result to me, if you don't have confidential data? Please upload your file to One Drive and share the link here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.