Count IDs with status changing across dates

[milomilo2020]

Hey, thanks for the help.

I have this table:

one action_id can have multiple ds_action_sk. On the other hand, each ds_action_sk canhave multiple status_id .

I need to create a DAX measure in order to visualize the change of status by insertion_date:

So the first time action_id = 2455617 appeared was on 11/5/2022 (We have a 1 on the 11/5/2022 representing the count of action_id. Then it appears again on 13/5/2022 but it has the same status_id = 0 ( Noticed in the table that we still have the count '1' on 13/5, as there is only one action_id (2455617 ) with status '0' , it doesn't take into account the ds_action_sk )

BUT on 14/5/2022 the action_id = 2455617 changes to '1', then in the table we see that the status 0 now has 0 counts and the status 1 starts to have a 1 count, because there is an action_id with status 1.

Summarizing, I need a measure to distinct count the action_id depending on the status. Then if the status changes, the count need to reflect 0. 

Thank again.

PBI FILE: https://drive.google.com/drive/folders/1Rd_JzzeRm_T-ZMoS_J8a3BXCVKq1N2LN?usp=sharing

[milomilo2020]

Hi @sturlaws , thanks for sharing this. The calculated column is working but not the measure. Please, let me attached a pbix with the sample data and the column/measure you've created. Hope this brings clarity to my requirement. Also there is a complete_date that only has the action_id with the status 1 (Closed)

https://drive.google.com/drive/folders/1Rd_JzzeRm_T-ZMoS_J8a3BXCVKq1N2LN?usp=sharing

Thanks again

[sturlaws]

Hi @milomilo2020,

what is your desired outcome? From how I read your description, it seems like you already have what you are looking for.

cheers,

Sturla

[milomilo2020]

HI, my desire outcome is the second table. It can be confusing that the status_id are 0 and 1. But this is different that the counts on the second table

[sturlaws]

Could you try to create a calculated column like this:

next_insertion_date =
VAR _action_id =
    CALCULATE ( SELECTEDVALUE ( 'Table'[action_id] ) )
VAR _date =
    CALCULATE ( SELECTEDVALUE ( 'Table'[insertion_date] ) )
VAR _tab =
    CALCULATE (
        MIN ( 'Table'[insertion_date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[insertion_date] > _date
                && 'Table'[action_id] = _action_id
        )
    )
RETURN
    IF ( ISBLANK ( _tab ), DATE ( 2022, 12, 31 ), _tab )

and then a measure like this:

Count status =
VAR _date =
    CALCULATE ( SELECTEDVALUE ( Dates[Value] ) )
VAR _status =
    CALCULATE ( SELECTEDVALUE ( 'Status'[status] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[action_id] ),
        FILTER (
            ALL ( 'Table' ),
            _date >= 'Table'[insertion_date]
                && _date < 'Table'[next_insertion_date]
                && _status = 'Table'[status_id]
        )
    )

You will also need a date/calendar dimension.

This will give you this, based on the data you provided:

[sturlaws · Accepted solution]

Use status_id from dimension instead of the fact_table in the table visual.

And in the measure replace this:

VAR _status =
    CALCULATE ( SELECTEDVALUE ( 'fact_ds_action'[status_id] ) 

with this:

VAR _status =
    CALCULATE ( SELECTEDVALUE ( 'dim_ds_status'[status_id] ) )