Forum Discussion
Dynamic Categories based on a drop down
I have data that looks like:
| Item |
| A |
| B |
| C |
| D |
And I want users to chose which items they want to put into which category (which may change over time). I created a new table:
| Categories |
| Option 1 |
| Option 2 |
Is there a way to display the Items and allow users to pick which ones they want assigned to Option 1, and in another table, pick which items they want in Option 2? Then, I can create a chart that shows the sales with the sum of all items in each category. I know how to do this manually by adding a new column to the Item table and I know how to make the chart but is there a way to do it dynamically in case a user wants to change which item is in which category without me having to go in and change it?
Really appreciate any help.
Anonymous ,
I think this is possible, Try using an IFELSE clause to the column and direct it to use the selected value in the slicer using the SELECTEDVALUE() DAX function whenever there is a filter being done on the slicer. If no slicer selection, then display the SWITCH() output. So roughly the DAX would look like below
ColumnName = IF( AND(HASONEFILTER(Category1), HASSONEFILTER(Category2)), <<Put the selected values here using the SELECTEDVALUE() DAX>>, SWITCH(<<expression>>))If this helps with your question, kindly appreciate with a kudo! If this solves, mark it as solution !!
Regards,
6 Replies
Hi Anonymous ,
I would not try to do this from inside Power BI. Instead have this table where the User is going to change the category in a sharepoint file or Onedrive and read that table into Power BI.
This way user can directly change the category as they want for the item and you don't have to manually change the file everytime.
Of course you need to refresh the Power BI file, once the category is changed by the user, which you can automate using Power Automate flows
It this helps, mark it as a Solution, Appreciate a Kudo !
Regards,
I'm not explaining myself properly. In my table:
Item A B C D I have added a column with a SWITCH statement assigning the different items to different categories. I would like to have two slicers 'Choose items for Category 1' and 'Choose items for Category 2'. Then users can pick which ever items they want in each category and that populates the new column instead of the SWITCH statement. Then this new column is used in the 'Legend' data for my plot. Is this possible?
Anonymous ,
I think this is possible, Try using an IFELSE clause to the column and direct it to use the selected value in the slicer using the SELECTEDVALUE() DAX function whenever there is a filter being done on the slicer. If no slicer selection, then display the SWITCH() output. So roughly the DAX would look like below
ColumnName = IF( AND(HASONEFILTER(Category1), HASSONEFILTER(Category2)), <<Put the selected values here using the SELECTEDVALUE() DAX>>, SWITCH(<<expression>>))If this helps with your question, kindly appreciate with a kudo! If this solves, mark it as solution !!
Regards,
Create 2 new tables each with all the available items in e.g.
Option 1 = DISTINCT( 'Table'[Item] ) Option 2 = DISTINCT( 'Table'[Item] )Do not connect these new tables to anything else, but use them to create 2 slicers on the page.
You can then create measures for each option, e.g.
Option 1 Sales = CALCULATE ( SUM ( 'Table'[Sales] ), KEEPFILTERS( TREATAS ( VALUES ( 'Option 1'[Item] ), 'Table'[Item] ) ) )If you wanted to do this for lots of measures, not just a simple sum, you could create a calculation group with a calculation item for each option. Just replace the SUM with SELECTEDMEASURE().
