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

[AlexandreCoutoB]

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?

[MFelix · Accepted solution]

Hi @AlexandreCoutoB ,

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 () ) )
    )

[AlexandreCoutoB]

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