Forum Discussion
Importing twice same table vs. complex relationship with DAX measure
Hi,
Let's take a simple mock example :
We want to compute and show in a table the sum of all invoices (all historic) per client, only for clients that have had an invoice for a given month.
My take would be to use a data model that import twice the fact table, the first one would be used to filter out the dim table (**Select the clients that have had invoices this month), the second one would be the usual fact table on which the aggregation measure is performed (**Sum of Amount of ALL invoices for the currently selected clients) :
Obviously, this is not optimal because the fact table is imported twice.
Could you please give me hints on how the same results can be obtained with DAX relationship function ?
Moreover, In general, can we face sometimes a situation where importing twice a table in a datamodel is conceptually the best options ? Or is it always better to build the relationship with DAX ?
Any help would be greatly appreciated, thanks in advance
4 Replies
hai_pbi Seems to me that you want to create a standard Date dimension table instead of importing the fact table twice.
Hi Greg_Deckler
Thanks for the rapid answer.
Adding a date dimension in indeed a must, I didn't put it here for simplicity.
Following what you suggested and the mock problem I set :
The Date dimension filters out the Fact table -> The Filtered out Fact Table Filters the Dim Table -> Lastly the Filtered out Dim table is used to aggregate some value on the whole (unfiltered) Fact table.
Could you help me with the last step ? I struggle to - inside a DAX measure - consider the dimension's element in the current context (filtered out by a slicer on the Fact table) and let them aggregate the whole fact table.hai_pbi Not entirely sure I follow that last step. Both the Date dimension and Client dimension would filter the Fact table. Now, if you want the Date -> Fact filter to then subsequently filter the Client dimension then you would need to set the relationship direction to Both (although most people will tell you to use CROSSFILTER within a measure instead, that doesn't necessarily work with slicers. If you then want to perform an aggregation against a completely unfiltered Fact table you can use ALL or ALLEXCEPT or REMOVEFILTERS within a CALCULATE for example. Again, not sure I am following completely.
