Forum Discussion
Dynamic Table in Power BI
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!
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
5 Replies
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/1447523The 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 Please provide sanitized sample data that fully covers your issue.
