Measure with subtotal count by column

Question

I have the following data:

Basically a normalized time series by country. My problem is, not all countries data ends on the same day.

I am trying to obtain column "DateCount", which would give me --for the date in that row -- the rowcount for ALL countries.

So for Jan18-to-Mar18 I would have 2 entries at each date (Italy and USA), but for April I would have only one (USA).

Any input on how to do this?

Many thanks!

RADO RADO · Accepted Answer · 2019-01-15T14:30:22

DAX formula:

DateCount = 
CALCULATE (
    DISTINCTCOUNT ( Table[Country] ),
    ALL ( Table[Country] ),
    VALUES ( Table[Date] )
)

Result:

How it works: It's a common pattern in DAX: Unfilter/Refilter. We change filters by first unfiltering countries (ALL) so that we can access all of them, then re-filter countries with the list of dates (VALUE supplies dates visible in current context). After desired filter context is established, you just need to count distinct countries.

Measure with subtotal count by column

1 Answers