Power BI and Drilling Across

• Vince Poirier

  SSC Eights!

  Points: 932

  More actions

  March 21, 2018 at 7:09 am

  #333955

  Hello,

  I'm new to Power BI.
  I'm trying to use Drill Across operations on 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 share no dimension except Date.

  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 Area part of the 3 Dimensions is conformed (structure and content) even though none of the dimensions are named Areas.
  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