Generate table dynamically using slicer value

[Ira_27]

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! 

[Ira_27]

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% 

[Ira_27]

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 

[MFelix]

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.

[Ira_27]

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)

[MFelix]

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%

[MFelix]

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.

[Ira_27]

Thank @MFelix but i dont want to run a Direct Query and hence i was looking for alternate options. If there is no way to run this adhoc using the imported data from pbi model then i will end up using DQ but wanted to reach out to the community to see if anyone encountered this.