topic Re: Running count with changing categories in DAX Commands and Tips

Running count with changing categories

Natsnebki — Tue, 15 Nov 2022 12:25:03 GMT

Hi,

I try to get the following matrix out of this data:

Data:

Date	CAT	Status
22/01/01	a	YES
22/01/01	c	YES
22/01/03	b	YES
22/01/05	c	NO
22/01/08	c	YES
22/01/10	a	NO
22/01/10	b	NO

As you can see the status may change over time.

Wanted matrix:

Dates	YES	NO	Total
22/01/01	2	0	2
22/01/02	2	0	2
22/01/03	3	0	3
22/01/04	3	0	3
22/01/05	2	1	3
22/01/06	2	1	3
22/01/07	2	1	3
22/01/08	3	0	3
22/01/09	3	0	3
22/01/10	1	2	3

So the count of the status by day., also for the dates there is no data.

I used a related table (DATESINTABLE) with all dates between the MIN date and the Max date from the source, and with the folllowing measure I got this result:

Measure:

Running Dcount status by dimdate =

CALCULATE (

DISTINCTCOUNT( 'Table'[CAT]),

FILTER (

ALLEXCEPT('Table', 'Table'[Status]),

'Table'[Date]<= MAX(DATESINTABLE[Dates])

)

MATRIX:

So I think i'm almost there, but maybe not.

Any help would be much appreciated

Thank you

Re: Running count with changing categories

daXtreme — Tue, 15 Nov 2022 14:54:46 GMT

ALLEXCEPT does not work the way you think it does. Please refer to https://dax.guide/allexcept for details.

Here's the code you'd most likely want:

// Base measure # CATs = DISTINCTCOUNT( 'Table'[CAT] ) // Base measure - RT # CATs RT = var LastVisibleDate = MAX( Dates[Date] ) var Result = CALCULATE( // YOu add 0 to be able to see // also the rows which would // have BLANKs. [# CATs] + 0, Dates[Date] <= LastVisibleDate, REMOVEFILTERS( Dates ) ) return Result

Re: Running count with changing categories

Natsnebki — Tue, 15 Nov 2022 15:06:21 GMT

Thank you for your suggestion. This measure adds 0's in my matrix instead of blanks, but the count is still the same, and not what i need. The fact that a cat has a changing status, is not counted correctly. I hope I'm clear

Re: Running count with changing categories

Jayee — Sun, 20 Nov 2022 06:38:14 GMT

Hi Natsnebki ,

Please refer to this Page for details about how to calculate running totals in DAX.

Re: Running count with changing categories

FreemanZ — Wed, 23 Nov 2022 05:48:00 GMT

Hi Natsnebki

try to create a new table with the code below:

Table = VAR Table1 = ADDCOLUMNS( SUMMARIZE( TableName, TableName[Date] ), "YES", CALCULATE( COUNTROWS( FILTER( TableName, TableName[Status] = "YES" ) ) ), "NO", CALCULATE( COUNTROWS( FILTER( TableName, TableName[Status] = "No" ) ) ) ) VAR Table2 = ADDCOLUMNS( Table1, "Total", [Yes]+[No] ) RETURN Table2

i tried and it works like this:

your dataset:

Re: Running count with changing categories

Natsnebki — Wed, 23 Nov 2022 13:50:38 GMT

I solved it by creating a table with all dates between min date and max date, merge this full outer, sort by category and date and fill down all nulls in Status.