cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bombom
Helper I
Helper I

Calculate values with same ID, but different secondary attribute

Hello!

I have a table called Logging. There columns Date, TicketId, Step and Result. Some TicketID have a Result = "Error", Step = 8, and if it was fixed later on, it will recieve new row with Result = "Successfull" and Step = 2.


DateTicketIdStepResult
01.01.20201234568Error
01.01.20201234562Successfull
02.01.20206543218Error
02.01.20206543212Successfull

 

There is also matrix with calculated measure that counts unique TicketId with Step = 8.


DateErrors_dist
01.01.20201
02.01.20201

 

I need to calculate new measure to look on TicketID with error, take this ID, look on its own table Logging ones again and try to find this ID with a Status = "Successfull".


DateErrors_distSolved_errors
01.01.202011
02.01.202011

 

The measures to calculate TicketID with errors look like this:

 

First: LogDistcount_ = DISTINCTCOUNT(Logging[TicketId])

 

Second: Errors_dist = CALCULATE(Logging[LogDistcount_], Logging[Step] = 😎

1 ACCEPTED SOLUTION
jgeddes
Solution Sage
Solution Sage

I started with the dataset...

jgeddes_0-1670276808356.png

I created a calculated column...

Resolved Tickets = 
var _isErrorTicket =
CALCULATE(MAX('Table (2)'[Step]), ALLEXCEPT('Table (2)', 'Table (2)'[TicketId]))
var _calc =
IF(
    AND('Table (2)'[Step] = 2, _isErrorTicket = 8),
    1,
    0
)
Return
_calc

And then wrote the measures...

Tickets with Errors = 
CALCULATE(
    DISTINCTCOUNT('Table (2)'[TicketId]),
    'Table (2)'[Step] = 8
)
Resolved Ticket Count = 
SUMX('Table (2)', 'Table (2)'[Resolved Tickets])

And ended up with...

jgeddes_1-1670276929111.png

Hope this gets you pointed in the right direction.

View solution in original post

3 REPLIES 3
jgeddes
Solution Sage
Solution Sage

I started with the dataset...

jgeddes_0-1670276808356.png

I created a calculated column...

Resolved Tickets = 
var _isErrorTicket =
CALCULATE(MAX('Table (2)'[Step]), ALLEXCEPT('Table (2)', 'Table (2)'[TicketId]))
var _calc =
IF(
    AND('Table (2)'[Step] = 2, _isErrorTicket = 8),
    1,
    0
)
Return
_calc

And then wrote the measures...

Tickets with Errors = 
CALCULATE(
    DISTINCTCOUNT('Table (2)'[TicketId]),
    'Table (2)'[Step] = 8
)
Resolved Ticket Count = 
SUMX('Table (2)', 'Table (2)'[Resolved Tickets])

And ended up with...

jgeddes_1-1670276929111.png

Hope this gets you pointed in the right direction.

@jgeddes  Everything worked, thank you! But, I forgot to mention. In the table could be duplicates with TicketID and the same Step number. For example, TicketID 123456 with a Step = 2 can meet in the table 4 times and the formula Resolved Ticket Count will sum up all 4. But how to calculate distinct? Only 1 of all these 4?

This should do it...

Resolved Ticket Count = 
CALCULATE(
    DISTINCTCOUNT('Table (2)'[TicketId]),
    'Table (2)'[Resolved Tickets] = 1
)

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors