cancel
Showing results for
Did you mean:
Regular Visitor

## How to calculate how many rows match a measure in other table?

Hi,

I have a table 'sla' with the following structure + other columns, such as date, engineer, etc.

ticket_id || company || sla_ok

1            || a              || yes

2            || a              || yes

3            || a              || no

4            || b              || yes

5            || b              || no

6            || c              || yes

And I have a table 'companies':

company

a

b

c

d

And I have a measure like:

sla_% =
total = CALCULATE(COUNT('sla'[ticket_id])

ok    = CALCULATE(COUNT('sla'[ticket_id],'sla'[sla_ok] = "yes")
return ok/total

Now I want to create a measure that calculates the % of companies that has at least 1 ticket and a sla_% bigger than 60%. This measure must also be dynamic to follow the slicers I have on the page (date, engineer, etc.)

So I'm trying to do the following, but it's not working correctly.

final_measure =

var ok = IF(CALCULATE([sla_%]) > 0.6, 1, 0)

var company_ok = sumx('companies', ok)
var company_total = calculate(distinctcount('sla'[company])

return company_ok/company_total

Does anyone know how to solve it, or can you explain if it's possible to do it dynamically?

1 ACCEPTED SOLUTION
Super User

Try the following measure:

``````final_measure =
VAR SLATotalTable =
ADDCOLUMNS ( companies, "Sla%", [sla_%] )
RETURN
DIVIDE (
COUNTROWS ( FILTER ( SLATotalTable, [Sla%] > 0.6 ) ),
COUNTROWS ( FILTER ( SLATotalTable, [Sla%] <> BLANK () ) )
)``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

2 REPLIES 2
Super User

Try the following measure:

``````final_measure =
VAR SLATotalTable =
ADDCOLUMNS ( companies, "Sla%", [sla_%] )
RETURN
DIVIDE (
COUNTROWS ( FILTER ( SLATotalTable, [Sla%] > 0.6 ) ),
COUNTROWS ( FILTER ( SLATotalTable, [Sla%] <> BLANK () ) )
)``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Regular Visitor

Hi @MFelix , yes, it worked! Thanks!!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors