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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!