cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## Dynamically Generate a Table from Information Distributed Across Data Structure

I have multiple tables in my data structure.

I need to auto generate a table that allocates volumes from the tblCtrMtrDetail table to the individual price indices to which the volumes have price exposure. The price indices to which each tblCtrMtrDetail record’s volume is exposed are determined from the price schedule associated with the contract and identified in the tblCtrAttributes file.  Each price schedule is a weighted average of various indices.  The weight for each index used in a price schedule is in tblSchedIndexDetail for which the primary key is Sched_id & index_id.

The generated table would ideally contain the following fields:

Ctr_Id, Mtr_Id, Prod Date, Price Schedule Id, Index Id, Index Proportion, Allocated Volume (calculated field)

4 REPLIES 4
Anonymous
Not applicable

You could create a table from Modelling tab using Distinct(tblCtrMtrDetai[Ctr_Id]), then using Lookup pull in all other columns.

Does this help?

Frequent Visitor

Thank you for your response.  That helped me with another problem.  Unfortunately,  I do not think that works for my posted problem.  Let me explain.

For each unique Ctr_Id in tblCtrMtrDetail, I need as many rows as their are indices associated with the price schedule associated with the Ctr_Id.  For example, for Ctr_Id =1 and Mtr_Id = 1, I have volume 100.  Ctr_Id =1 has an associated Price Schedule Id =1.  Price Schedule Id = 1, has 3 price indices associated with it Index_Id ={1,2,3} with Index_Proportions of {25%,25%,50%} for index 1,2,and 3 respectively.  For the given CtrMtr combination (1,1), I would need 3 rows in my auto-generated table.

for that scenario the three output table rows would need to be:

Ctr Id   Mtr Id   Sched_Id   Index_Id   Allocated Volume

1          1           1               1              25

1          1           1               2              25

1          1           1               3              50

Employee

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

The following are samples of the data in the tables relevant to my posted problem.

The following table relates to a contract entity and is used to relate a schedule to each contract.

Ctr_Id          Co_Id         Cust_Id      Sched_Id

 1 1 1 1 2 2 2 2 3 3 3 3 4 1 4 4 5 2 5 1 6 3 1 2 7 1 2 3 8 2 3 4 9 3 4 1 10 1 5 2 11 2 1 3 12 3 2 4 13 1 3 1 14 2 4 2 15 3 5 3 16 1 1 4 17 2 2 1 18 3 3 2 19 1 4 3 20 2 5 4 21 3 1 1

The following table is the detail for the rate schedules.  Each rate schedule is the weighted average of one or more indices.  The table contains a row for each index used in a given rate schedule and the weighting applied to the index in that given rate schedule.

Sched_Id  Index_Id  Index_Proportion

 1 1 0.25 1 2 0.25 1 3 0.25 1 4 0.25 2 1 0.5 2 2 0.5 3 1 0.333333 3 2 0.333333 3 3 0.333333 4 1 1

This table contains metered volumes assigned to a contract in a given month.

Ctr_Id      Mtr_Id    Volume          Prod Date

 1 9 5,810 12/1/2017 1 19 3,081 12/1/2017 1 20 2,388 12/1/2017 2 2 2,985 12/1/2017 2 34 488 12/1/2017 2 39 8,470 12/1/2017 3 11 4,946 12/1/2017 4 25 (7,056) 12/1/2017 4 40 5,687 12/1/2017 5 4 8,325 12/1/2017 6 16 8,883 12/1/2017 6 18 (9,178) 12/1/2017 6 24 (245) 12/1/2017 6 31 (6,818) 12/1/2017 6 37 (2,811) 12/1/2017 7 17 (2,447) 12/1/2017 7 30 2,288 12/1/2017 8 27 4,516 12/1/2017 9 14 7,995 12/1/2017 9 21 4,011 12/1/2017 9 29 6,184 12/1/2017 10 26 7,255 12/1/2017 11 7 (7,500) 12/1/2017 11 33 (4,271) 12/1/2017 12 10 4,029 12/1/2017 13 5 (1,131) 12/1/2017 13 28 (877) 12/1/2017 14 1 (258) 12/1/2017 14 36 (844) 12/1/2017 14 38 (6,577) 12/1/2017 15 12 (4,918) 12/1/2017 16 3 3,982 12/1/2017 17 15 5,153 12/1/2017 17 35 (9,369) 12/1/2017 18 6 (1,346) 12/1/2017 19 8 (917) 12/1/2017 19 13 9,838 12/1/2017 20 22 (4,111) 12/1/2017 20 32 (9,260) 12/1/2017 21 23 (1,758) 12/1/2017

The problem is to allocate the metered volumes per contract-meter-month record to the individual indices to which the metered volume is exposed.  I was hopeful of creating a table with the following headings:

Ctr_Id  Mtr_Id  Prod_Mo Sched_Id  Index_Id   Volume

such that when I added the volume for a given Ctr_Id Mtr_Id Prod_Mo across Index_Id's, the total would equal the volume for the Ctr_Id Mtr_Id Prod_Mo record in the 3rd table above.

By generating such a table, I can write one set of measures on that table that allows me to report at the level of detail I require.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors