Drill down based on dimensions from distinct tables

[Anonymous]

Hi guys, 

I have a PBI model that compile Facebook Ads, Google Ads and Linkedin Ads information in order to display Paid Media Performance.  For each platform, I've 2 table, one for ad's dimensions (like ad id ,  ad name, ad url link, ad creative, and so on...) and other for daily metrics, besides other dimensions tables. 

Model Relationships

I've also created a set of measures, in order to compute total Spend, Impressions, Clicks and so on. All my measures looks like this (just changing the columns that I'm summing):

Ads Spend =
CALCULATE (
    SUM ( 'f fb_ads'[spend_adjusted] ),
    USERELATIONSHIP ( 'f fb_ads'[anuncio_id], 'd fb_ads'[id] )
)
    + CALCULATE (
        SUM ( 'f g_ads'[cost] ),
        USERELATIONSHIP ( 'f g_ads'[anuncio_id], 'd g_ads'[id] )
    )
    + CALCULATE (
        SUM ( 'f linkedin_ads'[cost_in_local_currency] ),
        USERELATIONSHIP ( 'f linkedin_ads'[creative_id], 'd linkedin_ads'[id] )
    )

I'm trying to create a Matrix Visual that will give users the ability to see drill down till the ad level, by the ad id. I've created it till the Platform level, and the actual version looks like:

But as the ad ids are in separate tables (highlighted in different shades of green in the first image), I am not able to insert them as an additional level of drill down.

What would be the best solution for this situation? I thought about grouping all the fact tables in one, but that would require several changes. Is there any other more efficient way out?

Best regards,

[V-pazhen-msft · Accepted solution]

@Anonymous 
I guess you need to append the fact tables in to one, which is the most obvious method.

For now, you already have the relationship activated so I guess no need to use userelationship() function. Instead, try create inactive relationships between id columns in the 3 fact table and call the relationship with userelationship() function in measures. 

Regards
Paul