Forum Discussion
Generate table dynamically using slicer value
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 Ira_27 ,
Apologies for the question once again but trying to understand one thing, on your data you have the Mutual funds those are not appearing in your output why is that?
Also not getting what is you Holding % Doing the reverse calculation I get for example for Sec AStoick A 50 = 5000 * 1%For the first line is this the sum of the total Qty 690 / 10 = 1.45%