Forum Discussion
Distinct Count - Slicer does not work
This is probably a dumb issue for most of you, but I am very new to BI and I cannot get DAX, yet (hopefully one day I will!). I am trying to get a distinct count that updates based on a visual slicer. So here is a sample data
Name Dept Value
Julia HR 1
Julia HR 2
Leslie HR 2
Sean Accounting 1
Rob Finance 5
Rob Finance 2
The distinct count uses the Name column for the evaluation. I would like to have a result of 4 overall, or 2 if on my report visual I slice it by HR, or 1 if I slice the data by Finance.
Any help would be greatly appreciated.
Welcome EZgrafton !
You can put your name, dept, and value in a table visualization to get the data in the way you described it.
Then put your dept in a slicer visualization.
Now choose the name and put it in a card visual and select distinct count instead of the default count as follows
If nothing is chosen, it will show the distinct count of all names. Once you choose a specific department or a combination of departments, it will change accordingly counting the same name only once.
Hope this helps!
4 Replies
Welcome EZgrafton !
You can put your name, dept, and value in a table visualization to get the data in the way you described it.
Then put your dept in a slicer visualization.
Now choose the name and put it in a card visual and select distinct count instead of the default count as follows
If nothing is chosen, it will show the distinct count of all names. Once you choose a specific department or a combination of departments, it will change accordingly counting the same name only once.
Hope this helps!
Thank you SabineOussi; however, I forgot to mention that I also need the distict count to calculate the ratio by dept between the count of distinct Name and the total number of employees I have on another table.
Again, using the two tables below, the ratio for HR is the total EEs in HR (in this case 3) divided by the distinct count of HR EEs in table 1 (in this case 2) = 1.5
Table 1
Name Dept Value
Julia HR 1
Julia HR 2
Leslie HR 2
Sean Accounting 1
Rob Finance 5
Table 2
Name Dept
Julia HR
Leslie HR
Mark HR
Sean Accounting
Pat Accounting
Rob Finance
Bob Finance
Jessie Finance
EZgrafton Hopefully this helps, but it depends on your relationships between the tables. Since you haven't said anything about your model this assumes a relationship to a Dept dimension since you want to slice by department. My simple model looks like this.
Using Tabl3 as the slicer, the following Measures you need to create will return the ratio you want.
Tbl1CtName = DISTINCTCOUNT('Table1'[Name])
Tbl12CtName = DISTINCTCOUNT('Table2'[Name])
Ratio = DIVIDE([Tbl2CtName], [Tbl1CtName],0)
The first two measures need to be calculated in order to create the Ratio measure.
