Forum Discussion
Advocate IIDistinct Count - Slicer does not work
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!
Skilled SharerWelcome 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!
EZgraftonThank 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.
- SabineOussi
You can use the below measure
DIVIDE(DISTINCTCOUNT(Table2[name]) , CALCULATE( DISTINCTCOUNT(Table1[name]), ALL(Table1[dept]) ))
Not sure if it works for all cases.Let me know!