Forum Discussion
Join multiple tables to date table
So the question that needs to be asked is what about the data inside the 2 other tables inbetween are causing you to lose rows.
If you had a more simple structure of simply the Date table and the GL table, you might have very different results. Since those other 2 tables are part of your structure, their anomolies compound. You can't have the direct link, because then your model becomes ambigious (as there are 2 different possible outcomes from a single date filter).
For example, if you had a date of the 1st of January in your GL and Date table, but not within the Visit and SOP tables, then data for the 1st will not be shown from your GL table, as the other tables caused it to be filtered out.
If that is your current problem, your inactive link you have created is your plan B. Inside DAX you can use a formula called "Use Relationship" to force a particular measure to make a more specific calculation using your inactive relationship. This video discusses the concept in a very easy to understand way:
https://www.youtube.com/watch?v=2BxaUXlx3K4
Hi there
Thanks for the reply. That is what I thought, and yet as soon as I add date fields from my date table, I lose my $ amounts which come from the Marine_GL_Trans table.
When the slicer is cleared, the table reverts to all data, with amounts. So the existing relationships aren't sufficient to connect it to the DateTable.
So the question that needs to be asked is what about the data inside the 2 other tables inbetween are causing you to lose rows.
If you had a more simple structure of simply the Date table and the GL table, you might have very different results. Since those other 2 tables are part of your structure, their anomolies compound. You can't have the direct link, because then your model becomes ambigious (as there are 2 different possible outcomes from a single date filter).
For example, if you had a date of the 1st of January in your GL and Date table, but not within the Visit and SOP tables, then data for the 1st will not be shown from your GL table, as the other tables caused it to be filtered out.
If that is your current problem, your inactive link you have created is your plan B. Inside DAX you can use a formula called "Use Relationship" to force a particular measure to make a more specific calculation using your inactive relationship. This video discusses the concept in a very easy to understand way:
https://www.youtube.com/watch?v=2BxaUXlx3K4