Forum Discussion
Helper IIHow to create a dynamic table/Column based on a ranking?
Hello Folks,
I am trying to create a separate table based on ranking. As an example, when I select top 15 manufcturers from the provided slicers, , the table need to update top 15 manufacurers. If I select All, the same will be applied and the table need to update all manufacturers. What I need is, from that table, it is required to pass relationships to other fact tables.
Or If you can help me to create a separate calculated column based on the above requrement is okay for me. What business need is when the top N manufacturers selected from slicers, the same top N manufacturers need to show in the other pages. like 2019,2020 and 2021.
You can download the pbix file from this link.
https://drive.google.com/file/d/19mbM68onjax2qxF0khwQfe11OnplO6YW/view?usp=share_link
Try the following:
Output Required = CALCULATE ( SUM ( fTable[Sort Slicer Value] ), FILTER ( fTable, fTable[Product Code] = EARLIER ( fTable[Product Code] ) ) )Even though the you have to keep the yearly tables separate in the model, are you allowed to create a new table in the model? You could create a new table with only the summary values for all the years
7 Replies
PaulDBrownCommunity Champion
Ok, I've checked out the file.
The first question is: do you need the table for each year as a seperate table for any particular reason?
If the tables have the same structure, I would model the data differently by creating a single table containing all the years. This would solve the problem you are facing very easily.
SriGaG1PaulDBrown thank you for the reply. Actually no. I have presented you something similer to my business requrement. So we cannot change the data model eventhe tables. these are really large tables actually. and thats y
- PaulDBrown
In that case you are going to have to replicate all the measures for each of the years. You cannot create dynamic calculated tables or columns: these are calculated when the model is loaded, so they are impervious to filters and slicers. Anything dynamic has to be done with measures.
As regards the model, having a single fact table in your example is considered a best practice. It is not unusual to have fact tables with millions of rows.