Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Super User
Super User

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
jgeddes
Super User
Super User

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.