I’ve googled to try and find a solution to the issue below, but not found one that I’ve been able to apply. Any help on this is much appreciated!
Purpose
My intention is to have a dashboard presentation that is easy to update. I import cost data to the dashboard from a folder using PowerQuery. The macro for cleaning the data is then re-used on additional files added to the folder later on. That requirement for the easy update puts me into unfamiliar territory when I need to retrieve data from another data table that I can not directly connect to the imported data in the PowerPivot diagram view.
Data tables
I have a data table with the names and ID-s of “dentist clinics” and another with the “costs” related to 80 different clinics. A third “rebates” table contains the rebates for “lab costs”, which some have and others not. Clinics either pay full price for “lab costs” or a rebate. The tables are connected in the diagram view of Power Pivot. But, and here comes the problem: I can’t make a direct connection between the “cost” table and the “rebates” table, as the rebates change from year to year. It becomes an impossible “many-to-many” relation. I can make an indirect connection through the “clinics” table, which connects to both the “rebates” and the “costs” table in the diagram view.
The question
How to write the Measure (?) formula for the adjusted lab cost (rebate or no rebate) to be shown as a column in a Power Pivot pivot table with all the other 27 accounts of costs? I can use the RELATED function to easily get data into the “cost” table from the “clinics” table, but how do I get data from the “rebates” table into a pivot that should present the rebated lab cost together with all the other costs? A simpler solution than a MEASURE would perhaps be to add a Calculated Column and then use the RELATED function to “get hold” of the rebate for each individual row, but so far I haven’t’ been able to get RELATED to work in that scenario either.
All the best & hoping for someone to share their expertise 
Jan /Sweden
Hi Jan,
I think it would be better to bring the rebate into the costs table in Power Query where you can do a merge where the year and clinic ID are both criteria for finding/relating the rebate to the clinic.
Mynda
