CODEWITHSUNDEEP
ssaspowerbidax
vale_p
vale_p

Reputation: 125

Measure with subtotal count by column

I have the following data:

enter image description here

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!

Upvotes: 0

Views: 54

Answers (1)

RADO
RADO

Reputation: 8148

DAX formula:

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

Result:

enter image description here

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.

Upvotes: 2

Related Questions