Forum Discussion

lukelukas's avatar
lukelukas
New Member
3 years ago
Solved

Create a filter with custom string values

I am trying to create custom string filters that bring up different set or combinations of columns.   I have Columns representing model features like so:     For example, if I select "Mode...
Need Help
Tips and Tricks
  • kpost's avatar
    kpost
    3 years ago

    create a new table called 'Selection_Options' with a single column, 'Options',  with no relationships to other tables.

     

    Make the data in the Options column like this:

    Model1
    Model2
    Model3

     

    etc.

    Then, for each column  you'll have in your table visual, you'll have a measure like this:

    Column_1_Switch =
        VAR Selection =
        SELECTEDVALUE('Selection_Options'[Options], "All")
        RETURN
        SWITCH(
            TRUE(),
            Selection = "Model1", [Expression1],
            Selection = "Model2", [Expression2],
            Selection = "Model3", [Expression3]
            --etc.
        )



    Alright.  NOW, lastly, add a slicer to the page with your  'Selection_Options'[Options] column, and go to Visualizations> Format Visual> Visual> Slicer Settings> Single Select  and turn Single Select ON!


    Now, depending on what is selected in your slicer, each column can display a totally different measure.

    You may have to have the same number of lines in each switch statement, one for each model.  But that doesn't mean you can't have it return BLANK() if you want one model to have 4 columns and the other to have 3 etc.

    The caveat is the columns headers will be the SAME in each model.  So you may want to look into another solution if that is unacceptable to you.

kpost's avatar
kpost
Solution Sage
3 years ago

create a new table called 'Selection_Options' with a single column, 'Options',  with no relationships to other tables.

 

Make the data in the Options column like this:

Model1
Model2
Model3

 

etc.

Then, for each column  you'll have in your table visual, you'll have a measure like this:

Column_1_Switch =
    VAR Selection =
    SELECTEDVALUE('Selection_Options'[Options], "All")
    RETURN
    SWITCH(
        TRUE(),
        Selection = "Model1", [Expression1],
        Selection = "Model2", [Expression2],
        Selection = "Model3", [Expression3]
        --etc.
    )



Alright.  NOW, lastly, add a slicer to the page with your  'Selection_Options'[Options] column, and go to Visualizations> Format Visual> Visual> Slicer Settings> Single Select  and turn Single Select ON!


Now, depending on what is selected in your slicer, each column can display a totally different measure.

You may have to have the same number of lines in each switch statement, one for each model.  But that doesn't mean you can't have it return BLANK() if you want one model to have 4 columns and the other to have 3 etc.

The caveat is the columns headers will be the SAME in each model.  So you may want to look into another solution if that is unacceptable to you.

lukelukas's avatar
lukelukas
New Member
3 years ago

Thank you! This is exactly what I was looking for!
Would you have any tips on how to list column values in the table visual? What I mean is, instead of the [Expression] I would like to simply have column values like in my screenshot above?
Thanks for your help!

  • kpost's avatar
    kpost
    Solution Sage
    3 years ago

    Based on your example, here's what I would have for each of the 3 switch statements.

    Column_1_Switch =
        VAR Selection =
        SELECTEDVALUE('Selection_Options'[Options], "All")
        RETURN
        SWITCH(
            TRUE(),
            Selection = "Model1", COALESCE(SUM(Table(['Feature 1']), 0),
            Selection = "Model2", COALESCE(SUM(Table(['Feature 2']), 0)
           
        )

     

    Column_2_Switch =
        VAR Selection =
        SELECTEDVALUE('Selection_Options'[Options], "All")
        RETURN
        SWITCH(
            TRUE(),
            Selection = "Model1", COALESCE(SUM(Table(['Feature 2']), 0),
            Selection = "Model2", COALESCE(SUM(Table(['Feature 4']), 0)
           
        )

     

    Column_3_Switch =
        VAR Selection =
        SELECTEDVALUE('Selection_Options'[Options], "All")
        RETURN
        SWITCH(
            TRUE(),
            Selection = "Model1", COALESCE(SUM(Table(['Feature 3']), 0),
            Selection = "Model2", COALESCE(SUM(Table(['Feature 5']), 0)
           
        )




    • lukelukas's avatar
      lukelukas
      New Member
      3 years ago

      That's perfect, thank you so much