How to calculate a DAX measure over multiple (fact) tables?

[Anonymous]

Hello,
I have a question about working with 2 different fact tables. 1 = DirectQuery and 2 = imported.

How can I calculate a DAX measure over multiple fact tables in one single measure combined? And how to connect a DirectQuery fact table with an imported fact table?

For example:

I have factable 1 (DirectQuery) with the following columns:

• Date
• EventType (visits, clicks, impressions, etc)
• Multiple ID's to combine the fact table with multiple dimension tables
• SourceID (SourceID = 1 , 2 , 3)

I have factable 2 (import) with (almost) the same data from a different source:

• Date
• EventType (visits, clicks, impressions, etc)
• SourceID (SourceID = 4 , 5 , 6)

I used to combine these two fact tables with the Query Editor, but now we use DirectQuery for fact table 1 and imported fact table 2 in power BI desktop. So we can't combine them in the Editor, because we then need to import all the tables. And that's not what we want because of the amount of data (datamodel refresh problems).

In one measure I'd like to calculate the sum of visits, clicks, etc, in fact table 1 and fact table 2,
so I can visualise for both fact tables combined:

- The total visits per date

- The total visits per source 
- The totoal visits per dimension table 

Anyone who can help me with this case? Thank you very much in advance.
Rens

[Anonymous]

Thx @DataInsights 

That sounds like a simple solution for my problem! 

I've tried it this way before, but then I didn't have the relationships in my data model set correctly.
After changing some filter directions in the model it worked for me.

Thx!
Rens

[DataInsights · Accepted solution]

@Anonymous,

You can join each fact table to the dimension tables, and create measures using the following approach (Visits example):

Visits Table 1 = SUM ( Table1[Visits] )

Visits Table 2 = SUM ( Table2[Visits] )

Total Visits = [Visits Table 1] + [Visits Table 2]

As far as connecting the two fact tables--this is done by connecting them to shared dimension tables.