Forum Discussion

milomilo2020's avatar
milomilo2020
Frequent Visitor
4 years ago
Solved

Count IDs with status changing across dates

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

Need Help
  • sturlaws's avatar
    sturlaws
    4 years ago

    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] ) )

5 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    4 years ago

    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's avatar
      milomilo2020
      Frequent Visitor
      4 years ago

      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's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        4 years ago

        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: