Forum Discussion

purple_SP's avatar
purple_SP
Helper I
2 years ago

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.

"FILTER"
"SELECTEDVALUE"
DAX Switch()

2 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    2 years ago

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

     

    • purple_SP's avatar
      purple_SP
      Helper I
      2 years ago

      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?