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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
danextian
Super User
Super User

Dynamic Category Using a Slicer

Most of the time, I post on the community to ask for help but this time I am giving back.

I've read several posts that dynamic category based on slicer selection is not possible. While it may not be an out of the box feature, it is possible with some tweaking. I've also read similar solutions but I find mine simpler and easier to follow :). The trick makes use of the following:

  • adding an index column
  • referencing a query
  • unpivoting columns 
  • relationship

In my sample pbix (click to download from my GDrive), I connected  to the Ofeed data from powerbi.tips (click for the link to the webpage)

http://services.odata.org/AdventureWorksV3/AdventureWorks.svc

 and used CompanySales table. 

 

Here are the steps,

  1. After successfully connecting to CompanySales table, add an index column to it and just name it "Index". (We'll be using index number as unique identifier in a relationship.)
  2. Change Index data type to whole number. 
  3. Create another query referencing CompanySales table and name it "Category".
  4.  Remove all columns except ProductCategory, ProductSubCategory, OrderYear, OrderQtr and Index.
  5. Right-click Index column and Unpivot Other Columns.
  6. Rename these newly created columns: Attribute to Category, and  Value to Subcategory.
  7. Change data type of Category and Subcategory columns to text.
  8. Load the query.
  9. Create a relationship betwen CompanySales[Index] and Category[Index] (i set mine to both direction)
  10. Add Category column to a slicer visual (turn on Single Selection and off  Select All) 

You may view my sample report here:

 

https://app.powerbi.com/view?r=eyJrIjoiMjJmODEyNWYtNDk5OC00NjU1LTkzMjQtYzQ4YTQzNjA2MjgwIiwidCI6IjZjN...

 

Please don't forget to kudos this post if you find this helpful.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
11 REPLIES 11
Amedved
New Member

I found this concept very helpful! I will add that: if you have many categories that identify each row, keep as many of them as possible in your "Category" table. Thanks @danextian !

pablom76
New Member

I did this with my fact table of 500k records but it causes chart to load extremely slow any tips to fixing this issue
Momme
Advocate I
Advocate I

I managed to reproduce this today - great tool and an elegant solution, thanks!

 

I wonder: did you ever try to combine two identical and interdependent of these into one page? I failed, cause they block each other.

 

thx,

M

punit
New Member

Can you send pbix file for the same..

Thanks...

Hi @punit

 

The link to pbix is in my original post. It's publicly downloadable from my Google Drive.






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
punit
New Member

Can you share pbix file for the same...

Thanks

my problem is solved, what problem you are facing.

 

REGARDS...

WAQAR

waqar
Helper I
Helper I

i preapred a KPI dashbaord and use below KPI tile,i need to cummualtive monthly value in quarter via slicer to show respective data in KPI tile.

 

  • kPIIndicator.2.0.1

 

Can anybody help me out in this regards,

 

WAQAR...

+92 333 3035810

 

 

tmaz1234
Regular Visitor

Hi,

 

What did you use to create the 4 interactive tiles on top of your dashboard. Thanks

 

 

Hey @tmaz1234,

 

the interactive tiles are "just" a slicer with a horizontal layout. Go to the General Format options of the slicer and switch the orientation to horizontally.

 

Cheers



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

Well done!

 

Kudoed as commanded 😉



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.