Forum Discussion
Filtered Count from Another Table
- Anonymous7 years ago
The relationship will work. Just many to many can lead to ambiguous models. Anyhow, here's how I approached this one:
1. You are going to need a data table that will work as filter for Incidents and Orders:
make sure to use fields from that table as your slicers, filters, ect.
Then write a measure for Pending Incidents:
Pending Incidents = CALCULATE(COUNTROWS(Incidents),Incidents[Status]="Pending")
Then one for Pending and Service request from the Orders table:
Pending and Service Request = CALCULATE(COUNTROWS(Orders),Filter(Orders,Orders[Status]="Pending" && Orders[Type] = "Service Request"))
Then one last one to divide them out:
Pending Incidents divided by Pending and Service = DIVIDE([Pending Incidents], [Pending and Service Request])
Here's the final table. I cannot for life of me find the combo chart though:
I don't have access to the actual relationship dirgram, but I know that the only relationship between the two tables is the Order#
That's a many to many relationship, which can have some interesting results. Do you have the option to create another table using Power Query?
- achen7 years agoFrequent Visitor
Yes I could create another table if needed. But I am not sure what kind kind of table I would need to create in order to get what I am looking for.
- achen7 years agoFrequent Visitor
Sorry and I just noticed an error in my table above. The order # will always be unique. I had a typo there where O8 showed twice. The incident number will always be unique as welll. However htere can be mutliple incidents linked to one order.