Count of distinct values for each combination of two columns

[kzielinska]

Hi,

I have two tables.

For each unique combination of Employee and Location I want to count number of existing documents and create a new column in second table with the count only.

The result should be following:

In the report view, when I have a table with the final table, I also want to be able to filter out the dates based on the first table. So for example If I filter year 2000, for Jan & Poland I want to see Count of Documents = 1.

Can anyone help me with this?

[johnt75 · Accepted solution]

If you want it to react to slicers and filters then you need to do it as a measure not as a calculated column.

First create a couple of dimension tables which you can link to your documents table and then create a measure

// New Table
Employee = DISTINCT('Documents'[Employee])

// New Table
Location = DISTINCT('Documents'[Location])

// Measure
Num documents = COUNTROWS('Documents')

 Put the columns from your new tables into a visual with the measure.

[kzielinska]

Thank you! It works properly, but in some cases there are no documents for an employee, who is present only in the second table, and I also want to display this on my visual. What should I do in order to be able to see this information?

[johnt75]

You could change the measure to be

Num documents = COALESCE( COUNTROWS('Documents'), 0)

[kzielinska]

Thank you a lot! It works perfectly