Item name in hierarchy from another table

[yatinpurohit]

I have 2 tables. Expenses and Item Master. 

Fields in Expenses - ItemID, Expense Type, Amount

Fields in Items - ItemID, ItemName

The Expenses table has a many to one relationship with the Items on ItemID.

I want to create a bar chart with heirarchy which displays Amount by Expense Type and drill down to Amount by Expense Type and Item Name. Effectively, I should be able to drill down an expense type to items adding up to that expense. The created heirarchy in Expenses table works fine with ItemID. But I want to show the ItemName rather than the ItemID.  

I created a new calculated column Item Name = LOOKUPVALUE(Items[ItemName],Items[ItemId],Expenses[ItemId]). This also now works when I add the calculated Item Name field to the heirarchy and remove ItemID.

However, I want to know if this is the most optimized way to do this. Considering the relationship is already in place, do I really need a calculated column? Seems like duplication.

[v-piga-msft · Accepted solution]

Hi @yatinpurohit,

By my tests, it seems that if you want to create a hierachy in Power BI, you need to create a calcuated column for the current table.

If you don't want to create another column, you could create the bar chart with hierachy.

In addition, you could have a reference of this similar thread.

Best  Regards,

Cherry

[yatinpurohit]

Thanks. This works perfectly. You do not need to create hierarchy at all. Simply add columns in order of drill down to axis. Power BI will allow drill down of relevant.