Counting number of distinct values by date

[MartinLucas]

I'm trying to calculate the number of distinct values in a column by dates.

When trying to use DISTINCTCOUNT, it seems to count the number of occurrences for each distinct value, which isn't what I'm looking for.

My data looks like this, and I'd like to calculate the "Amount" column

	Worker	Amount
01-apr	A	3
01-apr	A	3
01-apr	A	3
01-apr	B	3
01-apr	B	3
01-apr	B	3
01-apr	B	3
01-apr	C	3
01-apr	C	3
01-apr	C	3
01-apr	C	3
02-apr	A	4
02-apr	A	4
02-apr	A	4
02-apr	B	4
02-apr	B	4
02-apr	C	4
02-apr	C	4
02-apr	C	4
02-apr	C	4
02-apr	D	4
02-apr	D	4
02-apr	D	4
02-apr	D	4
02-apr	D	4

Does anyone know how to do this?

[v-xulin-mstf · Accepted solution]

Hi @MartinLucas

Add index column:

= Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)

Try measure as:

Measure = 
CALCULATE(
    DISTINCTCOUNT('Table'[Worker]),
    FILTER(
        ALL('Table'),
        'Table'[Date]=MAX('Table'[Date])
    )
)

Here is the output:

The pbix file is attached.

Best Regards,

Link

[amitchandak]

@MartinLucas , Try a measure like

countrows(summarize(Table, Table[date], Table[Amount]))

or

countrows(summarize(Table, Table[date], Table[Worker], Table[Amount]))