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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

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.

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.