Forum Discussion

Natsnebki's avatar
Natsnebki
Frequent Visitor
3 years ago
Solved

Running count with changing categories

Hi,

 

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

Data:

DateCATStatus
22/01/01aYES
22/01/01cYES
22/01/03bYES
22/01/05cNO
22/01/08cYES
22/01/10aNO
22/01/10bNO

 

As you can see the status may change over time.

Wanted matrix:

DatesYESNOTotal
22/01/01202
22/01/02202
22/01/03303
22/01/04303
22/01/05213
22/01/06213
22/01/07213
22/01/08303
22/01/09303
22/01/10123

 

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

 

  • Natsnebki's avatar
    Natsnebki
    3 years ago

    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. 

5 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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

     

    • Natsnebki's avatar
      Natsnebki
      Frequent Visitor
      3 years ago

      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

       

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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:

     

  • Natsnebki's avatar
    Natsnebki
    Frequent Visitor
    3 years ago

    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.