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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lukelukas
New Member

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:

 

lukelukas_0-1690830853556.png

 

For example, if I select "Model 1" I want the table to show Column1, Column2, and Column3. If I select "Model 2" I want the table to show Column2, Column 4, and Column 5. The columns are not related which works fine and I can select columns from fields, but I can't figure out how to create a slicer/filter for custom strings like "Model 1".

 

Thanks very much.

 

1 ACCEPTED SOLUTION
kpost
Super User
Super User

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.

View solution in original post

5 REPLIES 5
kpost
Super User
Super User

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.

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!

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




That's perfect, thank you so much

Alex_Sawdo
Resolver I
Resolver I

You can "technically" do something like this with a field parameter. When building a field parameter, you can only select columns once but after it's initially built you can add rows as you choose by following the syntax in the definition. In addition, you can add another column to each element with the model numbers. Then, you can use the field parameter object and use this new column with the models as your slicer to dynamically choose which columns you need displayed.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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