How to create a hierarchy in Power BI with Lookupvalue.
- DAX function
- DAX function
- These functions might not work in
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
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
In Power BI, connect to Azure SQL Database, and select the table
SalesLT ProductCategory, choose
Open the Data interface.
This table has these columns:
There isn’t a column
ParentName. We need the string values of
ParentProductCategoryID to create a hierarchy of
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.
LOOKUPVALUE is like Excel