by Hi Everyone,
Today we will discuss another business use case based on slicer may be you didn’t excercise this but
it is very simple and powerful task which will help to slice and dice the data inversely.
Alright, lets see the business use case I have a slicer from dimension table, Now I want to exclude the selected value in the fact table based on the slicer selection. Well to be simple need inverse functioning slicer.
This can be achievable by purely DAX!
lets work on it, here is the sample data
Lets do this with the help of disconnected table,
Create disconnected Fact table through simple DAX,
Disconnected Fact = ALL(‘Fact’)
Now use the disconnected fact values in the matrix or table and use dimension field in the slicer.
Lets create measure,
Exclude = IF(ISFILTERED(‘Dimension'[City]), IF(MAX(‘Disconnected Fact'[City]) IN ALLSELECTED(‘Dimension'[City]), 1, 0))
Use the above measure and filter in the table or matrix IS 0
Now Filter the City = Chennai and see the table is removed the selected field in the slicer
did it! Yeah! but there is another edge case,
If we select all in the slicer the table does not show anything as per the use case,
Good Catch!? alright lets create one more measure and apply the filter in the table,
Exclude All = IF( NOT(ISFILTERED(‘Dimension'[City])),1,0)
Apply the Exclude All Is 0 filter in the table, now the table does not show anything when we select all in the slicer.
Done 🙂
we can also create duplicate dimension table and use the above mentioned DAX reversely, it is depending on the data size you dealt with it..!
