Newbi - Help Filtering Matching Values Within the Same Table

[JCtx]

Hello all! I'm new to Power BI and I'm sure there is a simple answer for this, but I'm stumped.

I have the following table:

Each ticket_number with the type "Incident" should have a value in the problem_id column which links that ticket_number to another ticket_number. In this case, ticket_numbers 4, 5, and 6 are related to ticket_numbers 1, 2, and 2, respectively.

I'm trying to create a table visualization which displays the following:

Column 1: The ticket_number of all tickets which have the type "problem".

Column 2: A count of all rows containing the problem_id that matches the ticket_number.

It should look like this:

Ticket # 1 has 1 ticket which references it as a problem_id (ticket #4)
Ticket #2 has 2 tickets which reference it as a problem_id (tickets #5 and 6)
Ticket #3 does not have any tickets which reference it as a problem_id but should still be displayed because it has the type "Problem".

Any help would be greatly appreciated.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here (my signature).

Hope this helps.

[Jihwan_Kim · Accepted solution]

HI,

I am not sure how your datamodel looks like, but please check the below picture and the attached pbix file.

Incident count expected result: =
VAR _currentrow =
    MAX ( Data[Ticket_number] )
VAR _t =
    FILTER ( Data, Data[Type] = "Problem" )
VAR _problem =
    FILTER ( ALL ( Data ), Data[Problem_id] = _currentrow )
RETURN
    IF ( COUNTROWS ( _t ) = 1, COUNTROWS ( _problem ) + 0 )

[JCtx]

Thank you for your help. This worked exactly as desired. It even accounted for another scenario I had not considered, which is if the problem_id for the current row is greater than the ticket_number.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here (my signature).

Hope this helps.

[JCtx]

Thank you for your help. This also achieved the desired result.

[Ashish_Mathur]

You are welcome.