nerdexam
Microsoft

70-466 · Question #82

You need to create the hierarchy in the AdhocReports project. What should you do?

The correct answer is D. Use the RELATED() function to consolidate the columns in the DimEmployee table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.. Building a cross-table hierarchy in a tabular model requires RELATED() to pull lookup-table columns into the fact or child table before creating the hierarchy.

Build a tabular data model

Question

You need to create the hierarchy in the AdhocReports project. What should you do?

Exhibit

70-466 question #82 exhibit

Options

  • AMulti-select all of the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.
  • BUse the RELATED() function to consolidate the columns in the DimSalesTerritory table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.
  • CUse the RELATEDTABLE() function to consolidate the tables, multi-select the columns in the hierarchy, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.
  • DUse the RELATED() function to consolidate the columns in the DimEmployee table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.

How the community answered

(18 responses)
  • A
    17% (3)
  • B
    6% (1)
  • C
    6% (1)
  • D
    72% (13)

Why each option

Building a cross-table hierarchy in a tabular model requires RELATED() to pull lookup-table columns into the fact or child table before creating the hierarchy.

AMulti-select all of the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.

Multi-selecting columns without RELATED() only works within a single table; columns from DimSalesTerritory are not available in DimEmployee without first bringing them in via a DAX relationship function.

BUse the RELATED() function to consolidate the columns in the DimSalesTerritory table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.

RELATED() must be authored on the many side of the relationship (DimEmployee, which holds the foreign key), not on DimSalesTerritory, which is the one side - applying it there reverses the relationship direction and produces incorrect results.

CUse the RELATEDTABLE() function to consolidate the tables, multi-select the columns in the hierarchy, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.

RELATEDTABLE() returns an entire related table as a scalar table value for aggregation; it cannot surface individual columns from a related table into the current table for use in hierarchy creation.

DUse the RELATED() function to consolidate the columns in the DimEmployee table, multi-select the columns, right-click the columns, and then click the Create Hierarchy command. Check in the changes before the next release cycle.Correct

The RELATED() function, applied in the DimEmployee table, traverses the many-to-one relationship to retrieve individual column values from DimSalesTerritory into calculated columns in DimEmployee. Once those columns exist in the same table, you can multi-select them and use the Create Hierarchy command to define a unified parent-child hierarchy.

Concept tested: Using DAX RELATED() to build cross-table tabular hierarchies

Source: https://learn.microsoft.com/en-us/dax/related-function-dax

Topics

#tabular hierarchy#RELATED function#DAX#dimension columns

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice