CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktopm
bombom
bombom

Reputation: 29

Power BI count distinct IDs with a condition

I have a data that looks like this:

Date TicketId Step Result
01.01.2020 13:00:00 123456 2 Successfull
01.01.2020 13:00:00 123456 4 Successfull
01.01.2020 13:00:05 123456 8 Error
01.01.2020 13:05:00 123456 2 Successfull
01.01.2020 13:05:00 123456 4 Successfull
01.01.2020 13:05:05 123456 8 Error
01.01.2020 13:10:00 123456 2 Successfull
01.01.2020 13:10:00 123456 4 Successfull
01.01.2020 13:10:05 123456 8 Error
01.01.2020 13:15:00 123456 2 Successfull
01.01.2020 13:15:00 123456 4 Successfull
01.01.2020 13:05:00 654321 2 Successfull
01.01.2020 13:05:00 654321 4 Successfull
01.01.2020 13:05:05 654321 8 Error
01.01.2020 13:10:00 654321 2 Successfull
01.01.2020 13:10:00 654321 4 Successfull
01.01.2020 13:10:05 654321 8 Error
01.01.2020 13:15:00 654321 2 Successfull
01.01.2020 13:15:00 654321 4 Successfull
01.01.2020 13:10:00 098765 2 Successfull
01.01.2020 13:10:00 098765 4 Successfull
01.01.2020 13:10:05 098765 8 Error
01.01.2020 13:10:00 098765 2 Successfull
01.01.2020 13:10:00 098765 4 Successfull
01.01.2020 13:10:05 098765 8 Error

From that data I have a matrix that calculates the distinct quantity of TicketIDs that have a step = 8 (Result = 'Error').

I need a DAX measure to calculate distinct number of TicketIDs if the TicketID have the Result = 'Successfull' as a last, before he had a Result = 'Error'. Note that next I will need to drillthrogh through the mesaure up to found TicketId.

Example of the desired output:

Date Count_distinct_TickedId_with_Errors Count_distinct_TickedId_with_Successfull_and_Errors_before
01.01.2020 3 2

It's a 123456 and 654321 TickedIds.

Current DAX measure to calculate distinct TickedIds with errors looks like this:

  1. LogDistcount_ = DISTINCTCOUNT(Logging[TicketId])
  2. Errors_dist = CALCULATE(Logging[LogDistcount_], Logging[Step] = 8)

And also, if it's not hard, the measure to calculate the TickedId, which is still have an Error result as at last.

Date Count_distinct_TickedId_with_Errors Count_distinct_TickedId_with_Successfull_and_Errors_before Count_distinct_TickedId_with_Errors_still
01.01.2020 3 2 1

It's a 098765.

Many thanks!

Upvotes: 0

Views: 2290

Answers (1)

Marcus
Marcus

Reputation: 4005

  1. Add a date column in Power Query. In Power Query, select your current "Date" column (which apparently does not contain dates!), go to "Add Column" and click "Date" -> "Date Only". This adds a new column called Date.1. Close and apply to get your date column into your data model.

  2. Add your measures:

Distinct_IDs_With_Errors = 
CALCULATE ( 
    COUNTROWS ( DISTINCT ( 'Table'[TicketId] ) ) , 
    'Table'[Step] = 8, 
    'Table'[Result] = "Error"
)

Distinct_Successful_After_Error = 
VAR _hasError = CALCULATETABLE ( DISTINCT ( 'Table'[TicketId] ) , 'Table'[Result] = "Error" )

VAR _table = 
ADDCOLUMNS (
    SUMMARIZE ( 
        FILTER ( 
            'Table' , 
            'Table'[TicketId] IN _hasError 
        ) ,
        'Table'[TicketId] ,
        "Max" , MAX ( [Date] )
    ),
    "Success_Last",
    VAR _max = [Max]
    VAR _last = 
        CALCULATE ( 
            SELECTEDVALUE ( 'Table'[Result] ) , 
            'Table'[Date] = _max
        )
    RETURN
        IF ( _last = "Successfull" , 1 )
)
RETURN
SUMX ( _table , [Success_Last] )

Distinct_Error_is_last = 
VAR _table =  
    ADDCOLUMNS ( 
        SUMMARIZE (
            'Table' ,
            'Table'[TicketId] ,
            "Max" , MAX ( 'Table'[Date] )
            ),
        "Last_Error" , 
        VAR _max = [Max] 
        VAR _last = 
            CALCULATE ( 
                SELECTEDVALUE ( 'Table'[Result] ) ,
                'Table'[Date] = _max
            )
        RETURN
            IF ( _last = "Error" , 1 )
    )
RETURN SUMX ( _table , [Last_Error] )

Used in a visualization:

enter image description here

Upvotes: 1

Related Questions