Forum Discussion
Best Solution for Month to Month Analysis without Causing Duplicate Data Errors.
Hi Lenihan,
What I was suggesting is that with the calculated table you can get all the unique CI Identifier across all your tables.
Then from tables you would create a relationship to the Calculated table.
What that then means is you could then use the Calcualted Table as a filter. And it will filter all the other tables?
Here is a blog post explaining how to create the Calculated Table - Connect any number of tables together via a common column
Can you use the CI data as a lookup table, and create a new data table that contains the Month and Status info, then join them?
For example:
That is how I have it right now, but when I go to add the new month of data, since the CI Identifier is the column I'm linking the relationship on, it won't allow duplicate values. Each month I add will duplicate every record. In my example, status was just one column, I have about 20ish that I use for various reports.
Hi Lenihan,
What you could do is to create a calculated table which has the unique CI Identifiers from both tables.Then create a relationship from each table to your calculated table?
Hi GilbertQ
So similar to what I was suggesting then with the CI identifier merged with the reporting month? If I create that calculated column on each table then use that as the relationship. I guess this would mean any measures i create I would need to use the dax function userelationship for any formulas that require data from both tables?
Hi Lenihan,
What I was suggesting is that with the calculated table you can get all the unique CI Identifier across all your tables.
Then from tables you would create a relationship to the Calculated table.
What that then means is you could then use the Calcualted Table as a filter. And it will filter all the other tables?
Here is a blog post explaining how to create the Calculated Table - Connect any number of tables together via a common column