Drilling Across multiple Fact tables

[VPoirier]

Hello,

I'm new to Power BI.
I'm trying to use Drill Across operations in Power BI.
I can't figure how to do it.
I have conformed dimensions in multiple stars in a DataMart
I wan't to drill up 3 different facts from 3 different stars and merge them together.

The 3 facts have different grains, they directly share no dimension except Date.

They each relate to a different dimension that contains a section about Areas.

The Area part of the 3 Dimensions is conformed (structure and content) even though none of the dimensions are named Areas.

My actual code wouldn't help much but here is some pseudo code that represent my intentions :
Q1 : SELECT AreaName, SUM(Mesure1) AS Mesure1 FROM Fact1 F INNER JOIN Dim1 D ON F.DimKey = D.DimKey GROUP BY AreaName
Q2 : SELECT AreaName, SUM(Mesure2) AS Mesure2 FROM Fact2 F INNER JOIN Dim2 D ON F.DimKey = D.DimKey GROUP BY AreaName
Q3 : SELECT AreaName, SUM(Mesure3) AS Mesure3 FROM Fact3 F INNER JOIN Dim3 D ON F.DimKey = D.DimKey GROUP BY AreaName

The result I expect is something like this :
AreaName | Mesure1 | Mesure2 | Mesure3

How can I achieve this in Power BI ?
Up until now, either I obtain a Cartesian product or Power BI creates wrong relations between the Dimensions based on the field AreaID, which is the Business Key but is not unique due to Type 2 versioning.

Thanks,

Vince

[v-jiascu-msft]

Hi Vince,

Please share a dummy sample. The solution could be as follow.

1. Create a new dimension table.

DimTable =
FILTER (
    DISTINCT ( UNION ( dim1, dim2, dim3 ) ),
    ISBLANK ( [DimKey] ) = FALSE ()
)

2. Establish relationships to the three fact table.

3. Create a table visual.

DimTable[AreaName]  Fact1[Measure1]  Fact2[Measure2]   Fact3[Measure3] 

Best Regards,

Dale