Generate dynamic hierarchy from the selection of grouping type from slicer.

[yogesh_shinde]

I am trying to work on a problem, which requires a column to be generated on the report when user selects a type of financial grouping.

There are 2 different tables, one table has the chart of account grouping which is static. The other table has the financial grouping which has only table names under the different levels which are required to be shown for the report.

Example - If user selects to view report by financial groupings then the levels of report should lookup to the column name in financial grouping table and then using that column name the respective column data from chart of accounts grouping should be populated in the report.

This is sample data for financial groupings reference table - 

Category	FSG_L_1	FSG_L_2	FSG_L_0
A	GL Sub-category	GL SubType	GL Account
A	GL Sub-category	GL SubType	GL Account
A	GL Sub-category	GL SubType	GL Account

chart of account grouping table - 

GL_Category_Description	GL_Sub_category_Description	GL_Type_Description	GL_SubType_Description
Income	Investment Income	Realized Investment Income	Currency
Assets	Current Assets	Other Current Assets	Other Current Assets
Assets	Current Assets	Inventory	Inventory
Assets	Current Assets	Other Current Assets	Other Current Assets
Assets	Current Assets	Loan Receivable	Related party
Assets	Current Assets	Loan Receivable	Related party

In this case if financial grouping option is selected, then Hierarchy on reports should be as follows - 

Category ( From Chart of Accounts - Static )

-- FSG_L_1 = GL Sub-Category => Refers to GL_Sub_category_Description
----- FSG_L_2 = GL SubType => GL_SubType_Description

I just want to explore if there is workaround to achieve this type of requirement on Power BI.

[V-lianl-msft]

Hi @yogesh_shinde ,

As far as I know, Power BI doesn't have support this now. You can only add the hierarchy manually instead of relying on slicers. Another workaround is to use the bookmark&button to switch the visual.https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-bookmarks?tabs=powerbi-desktop 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Sounds like you want to explore calculation groups.  Note that you cannot create columns on the fly but you can emulate that behavior with measures and calculation groups.