Forum Discussion

milomilo2020's avatar
milomilo2020
Frequent Visitor
4 years ago
Solved

Count distinct ID depending on status changing

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 .

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

 

 

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

BUT on 5/1/2022 the action_id = 122331  changes to Closed, then in the table we see that the status Open status now has 0 counts and the status Closed starts to have a 1 count.

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

(It has a measure but it is not working)

Thanks for the help

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi milomilo2020 ,

    According to your description, your sample file can't be opened, I create a sample and here's my solution.

    1.Create a date table contain all the dates you want to display.

    Date = CALENDAR(DATE(2022,1,1),DATE(2022,1,7))

    2.Create a measure.

    Count =
VAR _T =
    ADDCOLUMNS (
        GENERATE ( VALUES ( 'Date'[Date] ), VALUES ( 'Table'[action_id] ) ),
        "status",
            IF (
                MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[action_id] = EARLIER ( 'Table'[action_id] )
                            && 'Table'[insertion_date] = EARLIER ( 'Date'[Date] )
                    ),
                    'Table'[status]
                )
                    <> BLANK (),
                MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[action_id] = EARLIER ( 'Table'[action_id] )
                            && 'Table'[insertion_date] = EARLIER ( 'Date'[Date] )
                    ),
                    'Table'[status]
                ),
                IF (
                    'Date'[Date] < MIN ( 'Table'[insertion_date] ),
                    BLANK (),
                    MAXX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[insertion_date]
                                = MAXX (
                                    FILTER ( ALL ( 'Table' ), 'Table'[insertion_date] < EARLIER ( 'Date'[Date] ) ),
                                    'Table'[insertion_date]
                                )
                        ),
                        'Table'[status]
                    )
                )
            )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[action_id] ),
        FILTER (
            _T,
            [Date] = MAX ( 'Date'[Date] )
                && [status] = MAX ( 'Table'[status] )
        )
    ) + 0

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi milomilo2020 ,

    According to your description, your sample file can't be opened, I create a sample and here's my solution.

    1.Create a date table contain all the dates you want to display.

    Date = CALENDAR(DATE(2022,1,1),DATE(2022,1,7))

    2.Create a measure.

    Count =
VAR _T =
    ADDCOLUMNS (
        GENERATE ( VALUES ( 'Date'[Date] ), VALUES ( 'Table'[action_id] ) ),
        "status",
            IF (
                MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[action_id] = EARLIER ( 'Table'[action_id] )
                            && 'Table'[insertion_date] = EARLIER ( 'Date'[Date] )
                    ),
                    'Table'[status]
                )
                    <> BLANK (),
                MAXX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[action_id] = EARLIER ( 'Table'[action_id] )
                            && 'Table'[insertion_date] = EARLIER ( 'Date'[Date] )
                    ),
                    'Table'[status]
                ),
                IF (
                    'Date'[Date] < MIN ( 'Table'[insertion_date] ),
                    BLANK (),
                    MAXX (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[insertion_date]
                                = MAXX (
                                    FILTER ( ALL ( 'Table' ), 'Table'[insertion_date] < EARLIER ( 'Date'[Date] ) ),
                                    'Table'[insertion_date]
                                )
                        ),
                        'Table'[status]
                    )
                )
            )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[action_id] ),
        FILTER (
            _T,
            [Date] = MAX ( 'Date'[Date] )
                && [status] = MAX ( 'Table'[status] )
        )
    ) + 0

    Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.