Forum Discussion
Generate table dynamically using slicer value
Hello,
I was building a semantic model and ran into this issue. We daily holdings data and are required to show data for last 2 years. For each date the requirement is that user should be able to recursively decompose their holdings with the underlying rows of the parent.
I was able to achive this by creating a calcuated table using GENERATE function however each day results in over 4 Million record and just for one month it produces over 90M records. When the model was deployed this generated table bew up to caluclate for 2 years.
Hence i need help to see if there is a way to dynamically generate the table using selected slicer value to restrict the size of data or if there is a better way to store this data in semantic model.
Does powerbi allow a query to be used as a source and i can limit the logic in the query! I searched everywhere and couldnt find a solution!
7 Replies
Hi Ira_27 ,
Not sure if I understand what you want to achieve and how you want to get it, but going to your last question the answer is yes you can Power BI to get data from a query, you can even run stored procedures called trough a view in SQL code.
There is also an option of Dynamic parameters where you can define the inputs you want and using a Direct Query it can run you query dinamically.
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
Not sure if any of this can help out on the option you need but can lead you to a solution.
Hi MFelix ,
Probably i didnt post the question correctly, let me try again
Scenario:
- I have a table with Parent Child relation ship 90K records per day.
- I have to maintain rolling 24 months of data and using incremental refresh
- Decomposing each parent->child relation to the last child results in 4Million records per day
- I created a calculated table in pbix to decompose this parent child but ran into storage issues with calculated table
- Without this decomposition my size of model is about 7GB, as soon as i throw this the size grows beyond 25GB and refresh of the model breaks
Expected solution
- Is there a way that this calculated table can be generated on the fly using slicer selection to result to one day based on user selection
- Is there a method in pbix other than using direct query to dynamically create result table to be used in tablix or matrix
Hi Ira_27 ,
Let me start by apolozing for the questions but just want to make sure that I understand the requirement.
When you refer that you need to have the decomposion for each of the parent child what is exactly the information you need.
For example you have:
Parent
Child 0
0 1 1 2 2 3 2 4 You want to have a line for each one of this so you would get the following lines:
0 0-1 2-1-0 3-2-1-0 4-2-1-0 Is this understading correct?
Are you able to give a small sample of data like 2 or 3 parent child relations and what is the final outcome? A small mockup.
fyi, i am able to generate the data using GENERATE function. The problem is that when i deploy the model the calcualted table blows up because each date results in 80Million records.
I am looking for a way to either calculate this table on the fly for selected date and childid so that it limits the number of records or any other approach that will limit this decomposed data storage and run it adhoc
Here is the sample data and expected output.HoldingDate AccountID FundID SecID SecName Qty MV 2025-01-01 123 Sec A Stock A 10 100 2025-01-01 123 996 MF A Mutual Fund A 100 1000 2025-01-01 996 997 MF B Mutual Fund B 50 5000 2025-01-01 996 998 MF C Mutual Fund C 70 7000 2025-01-01 997 Sec B Stock B 10 100 2025-01-01 997 Sec A Stock A 70 700 2025-01-01 997 Sec C Stock C 80 800 2025-01-01 998 Sec D Stock D 90 900 2025-01-01 998 Sec E Stock E 100 1000 2025-01-01 998 Sec F Stock F 110 1100 Expected Output
HoldingDate AccountID FundID SecID SecName Qty MV 2025-01-01 123 Sec A Stock A 10 100 2025-01-01 123 Sec B Stock B 100 1000 2025-01-01 123 Sec A Stock A 50 5000 * (% holding) 2025-01-01 123 Sec C Stock C 70 7000 * (% holding) 2025-01-01 123 Sec D Stock D 10 100 * (% holding) 2025-01-01 123 Sec E Stock E 70 700 * (% holding) 2025-01-01 123 Sec F Stock F 80 800 * (% holding)
Hi MFelix,
I basically ended up using EVALUATE and running a paginated report from the model. This way i didnt have to store all the decomposed information in the model.
Basically what the percentage was that lets say if i am invested into a fund for $100 and the fund's MV is $1000 then i basically hold $100/$1000 = 1% of the fund hence decomposing the value will show every security that the Fund holds with a calculated MV of 1%
