Distinct Count - Slicer does not work

[EZgrafton]

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. 

[SabineOussi · Accepted solution]

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!

[EZgrafton]

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

[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!

[Seth_C_Bauer]

@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.