Dynamic Column Based on Slicer Selection

[ymulyana]

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 A 2014 10
MAT A 2015 15
MAT B 2014 12
MAT B 2015 14
MAT C 2014 6
MAT C 2015 8
YTD A 2014 10
YTD A 2015 15
YTD B 2014 12
YTD B 2015 14
YTD C 2014 6
YTD C 2015 8

in this sample same as : https://community.powerbi.com/t5/Desktop/Dynamic-Column-Based-on-Slicer-Selection/td-p/81126 But in more compleks columns. Any thoughts on how to do this?

[Eric_Zhang]

---

@ymulyana wrote:
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 A 2014 10
MAT A 2015 15
MAT B 2014 12
MAT B 2015 14
MAT C 2014 6
MAT C 2015 8
YTD A 2014 10
YTD A 2015 15
YTD B 2014 12
YTD B 2015 14
YTD C 2014 6
YTD C 2015 8

in this sample same as : https://community.powerbi.com/t5/Desktop/Dynamic-Column-Based-on-Slicer-Selection/td-p/81126 But in more compleks columns. Any thoughts on how to do this?

---

@ymulyana

The case in that link has 3 tables, could you post more details about your case, what is your expected output?

[powerbitz]

I would be interested in knowing if there is a solution for this as well.

My case is let's say I have the following three dimensions for sales data

1.  Category

2.  Product

3.  Subproduct

And I have the following measure

1.  Sales

What I want to do is provide the user a slicer that has a dropdown that contains the values "Category", "Product", "Subproduct"

When the user selects "Category" then a table shows

1.  Category

2.  Sales (Aggregated by Category)

When the user selects "Product" then a table shows

1.  Product

2.  Sales (Aggregated by Product)

In tableau, this is fairly straightforward but not in PowerBI and I am curious if PowerBI supports this use case.

thanks

[Ashish_Mathur]

Hi @powerbitz,

I dont think that is possible.  One will have to manually drag the dimension to the visual.

[parry2k]

@powerbitz can you share sample data in excel thru google drive or other means.

[Anonymous]

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.