Importing twice same table vs. complex relationship with DAX measure

[hai_pbi]

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

[Greg_Deckler]

@hai_pbi Seems to me that you want to create a standard Date dimension table instead of importing the fact table twice. 

[hai_pbi]

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.

[Greg_Deckler]

@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.

[hai_pbi]

Hi @Greg_Deckler 

Thanks for the tips !

To clarify what I want to achieve, let's focus on the initial mock problem I set :

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.

The "complexity" lies in the fact that we want, at the same time, to filter the dimension table based on an attribute of the Fact Table (a date) and then use the resultings selected dimension elements to filter a brand new Fact Table which isn't anymore filtered by its attribute Date.

The solution 1 was to play with the data model, importing twice the fact table, and using the Fact Table 2 to Filter out the Dimension table, and then use simple measures on Fact Table 1


Solution 2
I came up with a solution while keeping a simple star-scheme data model. But it is probably a bit overkill.

SumAmount For = 
VAR Considered_Clients = 
CALCULATETABLE(Dim Client,
        USERELATIONSHIP('Fact Invoices'[Date],'Dim Date'[ID]),
        CROSSFILTER('Dim Client'[ID],'Fact Invoices'[ClientKey],Both)
)
RETURN
CALCULATE(
        SUM('Fact Invoices'[Amount]),
        Considered_Clients
)

My questions :
* Is there a simpler option I'm not seeing ?
* I don't totally understand why solution 2 is working... does the table output from CALCULATETABLE keeps the same relationship than Dim Client, and therefore is able to Filter Out the Fact Table as well ?

Thanks a lot in advance for any inputs