Forum Discussion
bombom
Helper I
Helper ICalculate 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 Resul...
I started with the dataset...
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
_calcAnd 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...
Hope this gets you pointed in the right direction.
jgeddes
Super User
Super UserI started with the dataset...
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...
Hope this gets you pointed in the right direction.
bombomHelper I
Helper Ijgeddes 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?
- jgeddes
This should do it...
Resolved Ticket Count =
CALCULATE(
DISTINCTCOUNT('Table (2)'[TicketId]),
'Table (2)'[Resolved Tickets] = 1
)