How to create a hierarchy in Power BI with Lookupvalue.

PowerBI Lookupvalue Hierarchy

Requirements:

  • DAX function LOOKUPVALUE
  • DAX function PATH
  • These functions might not work in DirectQuery or Dual storage mode

Creating a column with DAX in Data vs M in PowerQuery

I am creating a hierarchy by creating columns using DAX in the Data interface. The changes can be seen in the Model. However, the changes aren’t reflected in PowerQuery. Why?

In a PBI forum I found this question. Why adding a column with DAX doesn’t update PowerQuery? The answer said that making modifications to the Model is unidirectional. Meaning that you can make changes in PowerQuery, which will reflect outwards into the Model. However, if you make changes in the Model, they won’t reflect inwards into PowerQuery.

A user also answered that they don’t recommend using DAX to modify the model, and that DAX should be used only for creating measures and aggregations. However, the Microsoft docs show using DAX to modify the model.

Change storage mode to Import

  • On Power BI, model interface
  • Click on the table
  • On the right, go to Properties, then Advanced
  • Change Storage mode to Import (if it was in DirectQuery)
  • It might ask to change to Dual, however the functions might not work in that mode

Setup an example with Azure SQL Databases

Follow this post on PowerBI with Azure SQL Database to create a sample database in Azure SQL Database with AdventureWorksLT.

In Power BI, connect to Azure SQL Database, and select the table SalesLT ProductCategory, choose Import storage.

Open the Data interface.

This table has these columns: ProductCategoryID, ParentProductCategoryID, Name

PowerBI Lookupvalue Hierarchy

There isn’t a column ParentName. We need the string values of ParentProductCategoryID to create a hierarchy of Category/SubCategory.

DAX LOOKUPVALUE is similar to Excel VLOOKUP

The Microsoft docs are pretty confusing about DAX LOOKUPVALUE function. This is similar to the Vlookup function in Excel. Given a value, search in a cell range where the lookup value should always be the first column, and return the column number containing the result value.

DAX LOOKUPVALUE is like Excel