Forum Discussion

achen's avatar
achen
Frequent Visitor
7 years ago
Solved

Filtered Count from Another Table

Hi,   New user to PowerBi here and I am having a very hard time getting what I think should be a simple calculation.   I have 2 tables. 1 is "Orders" and another is "Incidents" that are linked by...
  • Anonymous's avatar
    Anonymous
    7 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: