I will explain different ways I use slicers (filters) but I will skip its default behaviour because mostly it is quite intuitive to use and there are tons of articles easy to find in the internet.
If I have multiple categories for 1 single or multiple tables, I like to use this free plug-in called HierarchySlicer that can be downloaded from Microsoft website. It allows me to create individual new category tables that contain only customized criteria, once done, I just have to link them in “manage relationships” to my main tables. For Power BI to detect automatically the relationship, I will recommend that when you will create your new category table, put the same column name as you have in your data table. It allows me to have only 1 slicer instead of many for all tables and its behaviour is the same as the default slicer. Let´s jump to some DAX measures.
To inactive/hide a filter, for instance, I have those 3 filters:
With the slicer “type”, if I select “business”, I want that “support” is blank and vice versa:
I will create those 2 measures with this formula:
INT(SELECTEDVALUE('table'[argument])="value")
NOTE: replace “table”, “argument” and “value” by yours
For “business” and “support”, put the measure corresponding to them then once configure them like that, it is done:
There is no way to inactive filters, this trick is just hiding the options so if an option is selected, it will not disappear until I clear it:
To not affect this undesired selection in my calculations, I will add the ALL function. For my example, it will be:
To count number of selection:
For category1, “others” is on the second position, if you prefer to have it at the bottom of the list, add a new column and put a number. Once done, click on the menu “column tools -> sort by column” then select “number”:
IF no selection is done, it will display the total number of options, to put something else, I will need to use IF and ISFILTERED functions, for instance:
IF(ISFILTERED('table1'[argument1]),COUNTROWS(VALUES('table2'[argument2])),0)
To display what I select:
VAR cat1 = CONCATENATEX(VALUES('table1'[argument1]),[argument1],", ")
VAR cat2 = CONCATENATEX(VALUES('table2'[argument2]),[argument2],", ")
RETURN
SWITCH(TRUE(),
ISFILTERED('table1'[argument1]) && ISFILTERED('table2'[argument2]),cat1&", "&cat2,
ISFILTERED('table1'[argument1]),cat1,
ISFILTERED('table2'[argument2]),cat2,
"No selection")
The result of the measure 5 is not sorted, if you do care, you can use the same solution as category1 by adding a number column, sort it correctly during the creation or if it is already created, just edit the category:
As for the counting, if I select nothing, it will display all the options, to tell him something else, I will use the same thing, for instance:
IF(ISFILTERED('table1'[argument1]),CONCATENATEX(VALUES('table1'[argument1]),[argument1],", "),"No selection")
To show the list by ending with “and”, I will use the SUBSTITUTE function:
SUBSTITUTE(CONCATENATEX(VALUES('table2'[argument2]),[argument2],", "),","," and")
To put each selection in a line, I will use UNICHAR(10), there are 2 options:
To check if a specific option is selected or not, for instance, “comm”:
SEARCH("value1",[argument],"yes","no")
For 2 or more specific options, for instance, “comm” or “external”:
SWITCH(TRUE(),SEARCH("value1",[argument],1,0)>0,"yes",SEARCH("value2",[argument],1,0)>0,"yes","no")
And if I want both (“comm” and “external”), it will be:
SWITCH(TRUE(),SEARCH("value1, value2",[argument],1,0)>0,"yes","no")
I just have to make sure to match my measure 5 about how it displays my selection if I select those 2 words so if instead of comma, I have for instance a space, it will be “comm external” instead of “comm, external”.
This formula can be used to find a specific word in column too.
To cancel the selection, I will use CALCULATE and ALL functions, so for instance:
As you can see, for 2 categories, I put 2 times ALL, one for each category. This ignore option (or show me everything in spite of the selection) may be useful for some specific scenarios, for instance for calculating percentages and/or numbers.
To show only for a specific category, I will use IF and SELECTEDVALUE functions, for instance, to display only if any options are selected inside “support”:
IF(SELECTEDVALUE('table1'[argument1])="value1",CONCATENATEX(VALUES('table2'[argument2]),[argument2],", "),"value2")
But this formula will show “not support category” if I select other things outside of “support”. To remediate it, I will use CALCULATE and FILTER functions:
CALCULATE(CONCATENATEX(VALUES('table2'[argument2]),[argument2],", "),FILTER('table1','table1'[argument1]="value"))
This formula will show blank if there are no support options selected, to replace the blank to “not support category”:
IF(ISBLANK([argument]),"value",[argument])
When you are managing a team, “how to be a good manager” is the “must”...
As manager, I am doing many reports, even when I was an ITIL consultant, I still needed to do many reports...
ITIL V3 is going to be obsolete...
Managing an IT service when I start a new company is not an easy task, particularly true, if the service...