SSAS Tabular Queries Disregard Model’s Relationships Unless a Measure is Included (Except in Power B

[Anonymous]

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!

[v-sihou-msft · Accepted solution]

@Anonymous

In SSAS tabular, the DAX is directly executed by the VertiPaq Engine which will not determine the relationships between 'Group_By_Columns'. So when you execute the DAX in SSMS (same thing for other reporting tools since it will send the DAX to SSAS server to execute), it will crossjoin those group_by_columns. Once you put a measure, the VertiPaq Engine will have the group_by_columns slice the measure, and the unrelated rows will be aggregated.

However, Power BI uses a separate process running a special instance of Analysis Services, and it will generate the DAX internally.

Reference:
The VertiPaq Engine in DAX
Whitepaper: SSAS Tabular as Analytical Engine

Regards,