How to capture slicer value by DAX measure

Question

How to capture slicer value by DAX measure in all circumstances? Let's have sample data:

+-----------+---------+-------+
| category  | species | units |
+-----------+---------+-------+
| fruit     | apple   |     1 |
| fruit     | banana  |     1 |
| vegetable | carrot  |     1 |
| vegetable | potato  |     1 |
+-----------+---------+-------+

I added two measures:

Measure 1:

species selected = SELECTEDVALUE(Table1[species])

Measure 2:

IsFiltered = ISFILTERED(Table1[species])

Case 1. All items in both slicers selected.

Case 2. (problematic case). Fruits selected and Carrots selected (it is possible when we untie slicers interactions).

In case when we select fruit category from one slicer and carrot from another slicer there is a problem. This set of items is obviously empty. However definitely carrot from species have been selected and it is confirmed by IsFiltered measure which evaluates to True. Is there a way to capture that value in DAX measure?

Alexis Olson Alexis Olson · Accepted Answer · 2018-06-19T20:47:56

Since both the category and species slicers come from columns on the same table, if you have both fruit and carrot selected, then the resulting table is empty and any measures (except ones that remove both filters) will therefore be working with blanks. You cannot have both filters apply simultaneously an expect them to act independently (even if the two slicer visuals don't cross-filter).

If you don't want your species selected measure to be influenced by category, the simplest thing to do would be to turn off filtering (under Format > Edit interactions) from the category slicer to the visual containing species selected.

This isn't always what you want though, so another possibility is to create a new table for the species slicer which has no filtering relationship from Table1. This will allow you to work with the slicers selections separately if that's something you need to do. (I've definitely had to do this before when I wanted a slicer to behave more like a parameter than a filter.)

Edit: To do what I suggested, create a new Table2 in the query editor that references Table1, remove all columns other than species and remove duplicate if necessary. You should now have a single column table that is a list of unique species.

When you close and apply, Power BI will likely automatically create a relationship between the two tables, but you need to make sure it's exactly what you want. It needs to be a many to one relationship with a single filter direction.

Once this is done, you'll need to replace the Table1[species] slicer with Table2[species] slicer as well as change references in measures where necessary.

How to capture slicer value by DAX measure

1 Answers