Dynamic Text Formula Based on Filter Selected

DAX DAX Calculations
1

I am wanting to create a filter where the text dynamically changes based on what the options that are selected.

At the moment I have two groups which are called ‘Food’ and ‘Sub-Food’. I have three ‘Foods’ and each ‘Food’ has several ‘Sub-Foods’ underneath it. Please see below (or refer to attached PBIX file):

  • Fruit (Apple, Orange, Mango)

  • Vegetable (Carrot, Lettuce, Broccoli)

  • Meat (Beef, Pork, Chicken)

I would like to create a formula that displays the text based on the values selected (it concatenates) but if all values are selected in that particular food group, it will display the high level, e.g. Meat will be displayed if all the values in its ‘Sub-Foods’ have been selected, in this case, beef, pork & chicken

Desired Outputs

  1. If apple and mango are selected it will display: Apple, Mango
  2. If apple, mango and orange are selected (as all the values in the ‘Fruit’ group are selected), it will display: Fruit
  3. If apple, mango, orange and chicken are selected, it will display: ‘Fruit, Chicken’
  4. If apple, orange, mango, beef, chicken and pork are selected (these are all the values in the subgroup), it will display: Fruit, Meat

I’ve attached a PBIX file with the values. Thanks in advance. :slightly_smiling_face:

EDNA- Test 29122022.pbix (25.0 KB)

2

To create a formula that dynamically displays the text based on the selected options in your “Food” and “Sub-Food” columns, you can use a combination of IF and SWITCH statements. Here’s an example of how you can do this:

Copy code

=IF(COUNTROWS(VALUES(Table[Food])) = COUNTROWS(VALUES(Table[Sub-Food])),
  SWITCH(VALUES(Table[Food]), "Fruit", "Fruit", "Vegetable", "Vegetable", "Meat", "Meat"),
  CONCATENATEX(VALUES(Table[Food]), Table[Food], ", "))

This formula works by first checking if the number of selected items in the “Food” column is equal to the number of selected items in the “Sub-Food” column. If that’s the case, it means that all the items in the “Sub-Food” column have been selected, so the formula uses the SWITCH statement to return the corresponding high-level category (“Fruit”, “Vegetable”, or “Meat”). If not, it uses the CONCATENATEX function to concatenate the selected items in the “Food” column, separated by a comma and a space.

I hope this helps! Let me know if you have any questions.

Carlotta Krajcik
Microsoft SharePoint Services

3

Hi Carlotta,
Thank you so much for your help.

Your explanation makes sense but unfortunately I am still encountering some errors.
When I select, Broccoli, Carrot and Lettuce (as all values have been selected), the text ‘Vegetable’ should appear but it doesn’t.

image
image2445×1002 69.5 KB

I have attached the file with the code you have suggested. The measure is called ‘Title’.

Wondering if I could be missing something…

Thanks in advance :slight_smile:

EDNA- Test 29122022.pbix (26.6 KB)

4

Bumping this post

5

Hello @leontan.3
Due to the length by which this post has been active, we are tagging it as Solved.

Thanks to the contributors of this post.

For further questions related to this post, please make a new thread. Please feel free to reopen this thread if anyone would like to answer the pending inquiry above.

Thank you