Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ymulyana
Frequent Visitor

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 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?
5 REPLIES 5
Eric_Zhang
Employee
Employee


@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?

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

Hi @powerbitz,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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