Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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: C...
  • Thejeswar's avatar
    Thejeswar
    1 year ago

    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,

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 year ago

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().