Forum Discussion
Dynamic Column Based on Slicer Selection
I have a situation where users want the ability to have a column change the data presented based on a slicer selection. The analogy would be: 1. Table Sales Consist of : Period Group Year Sales MAT ...
Hi powerbitz,
I dont think that is possible. One will have to manually drag the dimension to the visual.
This is possible, will dig out the solution in a model I have done for this. It involved quote extensive power query manipulation to get the result. It involved two tables. 1 will be your current fact table with an ID column. Then I made 1 mega unpivoted table in power query with columns id, dimension category, dimension.
The mega table involved making a take for each dimension where I would reference my fact table, delete all columns except id and the dimension, add a column with the name of the dimension (dimension category). Then I would union each dimension table together, then disabled the individual dimension tables from loading.
Then if you create a relationship between the fact table and the mega table you should be able to get it all working smoothly with the same measures you would use across your fact table. However you use the mega table for your axis and slicers.
Might sound quite long winded however was the most efficient approach I came up with in the end. Other approaches as taking 2 minutes per, this method is around 1 second (40 dimension, 10 mill fact table)
Let me know if you have any questions about this approach and I will write a better response on my laptop instead of phone.