Forum Discussion
Newbi - Help Filtering Matching Values Within the Same Table
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.
Hi,
You may download my PBI file from here (my signature).
Hope this helps.
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 )
5 Replies
Hi,
You may download my PBI file from here (my signature).
Hope this helps.
Thank you for your help. This also achieved the desired result.
You are welcome.
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 )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.
