topic Re: Dynamic Table in Power BI in Developer

Dynamic Table in Power BI

lfholland — Fri, 25 Feb 2022 15:01:54 GMT

I need to create a dynamic table upon load. For example, I am developing our finance dashboard where all account codes that start with 100 are part of the "ASSETS" group. So, when the data is refreshed, if there is a new account code added that begins with 100, in a column labeled "Header 1" it is labeled ASSETS. There are thousands of account codes and more get added on a regular basis, so maintaining in excel wouldn't be very efficient. I have other headers as well, and as it gets loaded, it would end up looking something like the image below.

We currently do this in QlikView by associating a group of account codes to a specific variable and then build a table based off those variables. I am basically re-designing it in Power BI and I'm still learning!

Does anyone have any resources or know of videos I can watch to learn how to accomplish this?

Bottom line, the account code is loaded from our data warehouse but the headers need to be assigned dynamically as they are not available fields where our financial data is held.

Thank you in advance!

Re: Dynamic Table in Power BI

lbendlin — Sat, 26 Feb 2022 18:41:44 GMT

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Dynamic Table in Power BI

lfholland — Mon, 28 Feb 2022 16:15:15 GMT

The screen shot I posted is my expected outcome; however, I can provide a little more variety as to what I need. Below is a sample of what I need.

I'm wondering as another solution if there is a type of "wildmatch" that can be used in an if statement? For example if the first character of the account code is 1 or 2, Header_1 is "ASSESTS". If the account code begins with 100, Header_2 is "cash", etc.

ACCOUNT_CODE	Header_1	Header_2	Header_4	Header_5
1002330	ASSETS:	Cash	Total Assets	Balance Sheet
1002400	ASSETS:	Cash	Total Assets	Balance Sheet
1002401	ASSETS:	Cash	Total Assets	Balance Sheet
1002402	ASSETS:	Cash	Total Assets	Balance Sheet
1002403	ASSETS:	Cash	Total Assets	Balance Sheet
1002404	ASSETS:	Cash	Total Assets	Balance Sheet
1300	ASSETS:	Total AR	Total Assets	Balance Sheet
1300100	ASSETS:	Total AR	Total Assets	Balance Sheet
1326	ASSETS:	Total AR	Total Assets	Balance Sheet

Re: Dynamic Table in Power BI

lbendlin — Mon, 28 Feb 2022 19:48:44 GMT

Please provide sanitized sample data that fully covers your issue.

Re: Dynamic Table in Power BI

Anonymous — Fri, 04 Mar 2022 01:46:28 GMT

Hi @lfholland,

I think you need to create a parameter that stored the rule to remark records, then you can add a calculated column to your fact table and lookup at the above parameter table rules, and compare with the current row value to setting corresponding groups.

SWITCH function (DAX) - DAX | Microsoft Docs

From SQL to DAX: String Comparison - SQLBI

Regards,

Xiaoxin Sheng

Re: Dynamic Table in Power BI

lfholland — Fri, 04 Mar 2022 13:51:46 GMT

I think this will work. I'll test it out and let you know. Thank you!!!