SSAS Tabular Queries Disregard Model’s Relationships Unless a Measure is Included (Except in Power BI !)… Why?

• djb_77v

  SSC Veteran

  Points: 273

  More actions

  March 15, 2017 at 6:42 am

  #148044

  Here is an example that drives me nuts. I have an SSAS tabular model consisting of a parent/child relationship between 2 tables:
  Table 1) REGION (logical parent of Area)
  Table 2) AREA (logical child of Region)

  The REGION table contains these records:
  Region_ID    Region_Name
1       EAST
2       WEST

  The AREA table contains these records:
  Region_ID    Area_Name
1       NORTHEAST
1       SOUTHEAST
2       NORTHWEST
2       SOUTHWEST

  The two tables are related in SSAS by the Region_ID field (bi-directional).
  If I do a query of the Region Name and Area Name in Power BI using the tabular model, I get the correct result:
  Region_Name       Area_Name
EAST         NORTHEAST
EAST         SOUTHEAST
WEST         NORTHWEST
WEST         SOUTHWEST

  ISSUE: If I do a DAX query of the Region Name and Area Name using SSMS, Report Builder or even Tableau, I get a “cross join” between the two tables, UNLESS I ALSO INCLUDE A MEASURE:
  Region_Name       Area_Name
EAST         NORTHEAST
EAST         SOUTHEAST
EAST         NORTHWEST
EAST         SOUTHWEST
WEST         NORTHEAST
WEST         SOUTHEAST
WEST         NORTHWEST
WEST         SOUTHWEST

  If I include any measure in my DAX query, the issue goes away:
  Region_Name   Area_Name  Area_Count
EAST     NORTHEAST  1
EAST     SOUTHEAST  1
WEST     NORTHWEST  1
WEST     SOUTHWEST  1

  Why does this work one way (the right way) in Power BI and another (wrong) way everywhere else in the world? Am I modeling it wrong in SSAS? Can I include something in my DAX query to make it behave? I'm a newcomer to SSAS tabular and DAX, but I've been doing tabular modeling in SQL Server since the old Report Models that were last seen in 2008R2.

  Sometimes I want to write a query that just lists some fields in my SSAS source, and filter it on some attributes… No measures are required. So, it is important that this works correctly (other than in Power BI).
  Any help is greatly appreciated. Thanks for reading!