Multiple fact tables -> date table with currency conversion?

[Vegard1985]

Hi experts,

Sorry, I haven't been able to find a similar question. Just a small background story:

We're trying to construct a "down to up" P&L where anything linked to gross profit comes from one report/dataset (a sales report), and cost comes from another report/dataset (an opex report). 

The sales report is the largest dataset (I guess naturally), with a lot of dimensions, a date calendar etc. The opex report wasn't created yet, and my thought initially was to create the opex report as simple as possible, and connect this report to the sales report's date calendar since this already was constructed. There's also built in a "multiple source to multiple target currencies conversion" in the sales report following the logic of daxpatterns linked here: daxpatterns/currency-conversion . Here I'm starting to struggle a bit. 

Just for the sake of information, the opex report is published to PBI service and connected to the "original sales report" as a PBI dataset. This new combined report is also published to PBI service, and we've created another report pulling this combined dataset (seems a bit much I guess, but we wanted to have a "clean combined dataset" and have measures in a new report, partly to also test composite model functionality).

Is this at all doable? Can we have multiple fact tables connected to the date calendar and a currency conversion model?

Do you see if there's anything that would break or cause issues with this model? Would any of the relationships need to have the cross filter direction set to both?

[MFelix · Accepted solution]

Hi @Vegard1985 ,

There is no issue to have several table connected to the date table and the currency conversion model, the only question is that you need to be sure that the relationships don't create any link between the facts that will generate inactive relationships (but believe will not happen).

Regarding the question about the cross filtering I think is better not to active it and then make use of the CROSSFILTER function on the measures if needed. Chech the video below:

https://www.youtube.com/watch?v=44JKwL3Js10

[Vegard1985]

Hi Miguel - thanks, and thanks for the tip, makes sense!

[MFelix]

If you have any issues while developing the solution please tell me.