Power BI Create a Hierarchy with Lookupvalue
How to create a hierarchy in Power BI with Lookupvalue.
Requirements:
- DAX function
LOOKUPVALUE
- DAX function
PATH
- These functions might not work in
DirectQuery
orDual
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
, thenAdvanced
- Change
Storage mode
toImport
(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
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