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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
purple_SP
Helper I
Helper I

Filter a table based on multiple selected values

Hi all, I am currently using a switch() statement within a measure to filter a table appropriately depending on a selection like so:

 

 

_Orders_BY_superhero_preference = 
VAR SelectedOption = SELECTEDVALUE(SuperHeroes[name])
VAR code = SELECTEDVALUE(SuperHeroes[code])
VAR all_codes = SELECTCOLUMNS(SuperHeroes,"code",[code])

RETURN CALCULATE([_Orders],FILTER(OrderTable, OrderTable[matched] = "Y" && OrderTable[order_number] IN all_codes && SWITCH(
            SelectedOption,
            "Batman", RELATED('CustomerAttributes'[Fav_superHero]) = "Batman" && CONTAINSSTRING(OrderTable[order_number],code),
            "Spiderman", RELATED('CustomerAttributes'[Fav_superHero]) = "Spiderman" && CONTAINSSTRING(OrderTable[order_number],code),
            "Superman", RELATED('CustomerAttributes'[Fav_superHero]) = "Superman" && CONTAINSSTRING(OrderTable[order_number],code)
        )))
       

 

Where the measure [_Orders] is basically a DISTINCTCOUNT of order ids, and the rows included in the aggregation are determined by which superhero name is selected.

 

This works with a single option eg: name = Batman.

 

However, is there a way that I can modify this so that it works with multiple selected options?

 

In other words if Both Batman and Spiderman are selected, the OrderTable is filtered such that it contains rows where the 'matched' column = "Y", the order_number value exists in the SuperHeroes code table, and only rows where the related values in the customerAttributes table are Batman or Spiderman are included in the aggregation? 

 

I've tried replacing SELECTEDVALUE with VALUES, but having some trouble with figuring out how to account for multiple selections within the switch logic.

2 REPLIES 2
Mahesh0016
Super User
Super User

@purple_SP  I hope this helps you. Thank You!

_Orders_BY_superhero_preference =
VAR SelectedOption =
SELECTEDVALUE ( SuperHeroes[name] )
VAR code =
SELECTEDVALUE ( SuperHeroes[code] )
VAR all_codes =
SELECTCOLUMNS ( SuperHeroes, "code", [code] )
RETURN
CALCULATE (
[_Orders],
FILTER (
OrderTable,
OrderTable[matched] = "Y"
&& OrderTable[order_number]
IN all_codes
&& SWITCH (
SelectedOption,
"Batman",
RELATED ( 'CustomerAttributes'[Fav_superHero] )
IN { "Batman", "Spiderman", "Superman" }
&& CONTAINSSTRING ( OrderTable[order_number], code ),
"Spiderman",
RELATED ( 'CustomerAttributes'[Fav_superHero] )
IN { "Batman", "Spiderman", "Superman" }
&& CONTAINSSTRING ( OrderTable[order_number], code ),
"Superman",
RELATED ( 'CustomerAttributes'[Fav_superHero] )
IN { "Batman", "Spiderman", "Superman" }
&& CONTAINSSTRING ( OrderTable[order_number], code )
)
)
)

 

Thanks for your reply @Mahesh0016 .

It definitely helps. Would there be a way to account for cases where the different selected values require filtering different columns though?

 

For example say that the customer attribute table looked like this:

Customer idFavourite is BatmanFavourite is SpidermanFavourite is Superman
123124
456619
789961

 

And lets say I wanted to filter customers based on three segments:

-If selected superhero is Batman, filter all customers where "Favourite is Batman" = 1

-If selected superhero is Superman, filter all customers where "Favourite is Superman" = 1

-If selected superhero is Spiderman, filter all customers where "Favourite is Spiderman" = 1

 

At the moment I can modify my original code like so:

 

_Orders_BY_superhero_preference = 
VAR SelectedOption = SELECTEDVALUE(SuperHeroes[name])
VAR code = SELECTEDVALUE(SuperHeroes[code])
VAR all_codes = SELECTCOLUMNS(SuperHeroes,"code",[code])

RETURN CALCULATE([_Orders],FILTER(OrderTable, OrderTable[matched] = "Y" && OrderTable[order_number] IN all_codes && SWITCH(
            SelectedOption,
            "Batman", RELATED('CustomerAttributes'[Favourite_is_batman]) = 1 && CONTAINSSTRING(OrderTable[order_number],code),
            "Spiderman", RELATED('CustomerAttributes'[Favourite_is_Spiderman]) = 1 && CONTAINSSTRING(OrderTable[order_number],code),
            "Superman", RELATED('CustomerAttributes'[Favourite_is_Superman]) = 1 && CONTAINSSTRING(OrderTable[order_number],code)
        )))

 

But this causes issues when two or more values are selected.

 

If I select Superman & Batman, is there a way to filter all customers where "Favourite is Batman" = 1 OR "Favourite is Superman" = 1?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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