I have two sets of data 1) Control Totals - Containing year, country, air mode estimates, and all modes estimates and 2) Survey Data that contains a Flight date (but no separate year field), country, and other survey values. Both tables are linked to a Dim table for translating a three digit country code to a text value for reporting. I have attached a screenshot of my model (or at least part of it). I would like to calculate expanded estimates from the weighted percentages shown in the other attached screenshot. I am trying to wrap my head around how to connect the two tables. I know I can create a measure that can contain the weighted % that I need to multiply by the control total from I remember in the one of the trainings (I have done power query, power pivot, and power bi) something about the DAX function RELATED, but am not sure how to setup that link. Can you help me? I will need to be able to do this for other tables as well that will contain unpivoted data columns that are my next issue to figure out...
Hi Laura,
In order to use the RELATED function you need the tables to actually be related, which as you're aware, they aren't and it doesn't appear from what you've shown me that they can be related. That is, there doesn't appear to be a common field between the Control Total table and the Survey Data table. The DimOSCountries table's filter direction is down to both the Survey Data table and Control Totals table, so it's no help.
Note: the relationship between the Control Total and Dates tables should be single directional i.e. Dates filters Control Total.
It's not clear what table your weights are in, but I suspect they're not at the correct level of detail to be of use the way you want.
What I recommend you do is create a mockup of your model in Excel with a small but comprehensive set of your data that you can share with me, as there's not much I can do without seeing your model.
Mynda
