Count of IDs by distinct certs

[Anonymous]

Hello need some help to find  the best way to count

I have a table with some calls IDs, each call has a working area, a model and a cert1 and cert2 columns, so for each call id, there´s a model and one or two certs asociated.

callID	Working zone	model	cert1	cert2
1	1	modelA	certA
2	3	modelB	certB	certW
3	2	modelC	certB	certX
4	2	modelX	certC	certY
5	1	modelA	certA
6	1	modelY	certC	certY
7	3	modelC	certB	certX

I have another table with techs ID´s and the certs they have like:

ID	Cert
111	certA
111	certB
111	certC
111	certY
112	certC
112	certY
221	certA
221	certX
221	certW
222	certB
331	certB
331	certW
331	certX
332	certB
332	certW
332	certX

In the middle i´ve a table with tech´s info like:

techID	techName	working zone
111	Jhon	1
112	Jamal	1
221	Jim	2
222	Julius	2
331	Joe	3
332	James	3

The relationship is like: calls working zone ->Tech´s working zone and Tech´s ID -> certs ID

I´m trying to find by working zone the #calls and the number of guys that have cert1 AND cert2 asociated to that certs:

Working zone	Certs	#Calls	#Techs with both certs
1	certA	2	1
1	certC AND certY	1	2
2	certB AND certX	1	0
2	certC AND certY	1	0
3	certB AND certW	1	2
3	certB AND certX	1	2

So far i have a the # calls per working zone and tech with one or the other cert, but can't calculate the number of techs with both certs.

Here´s what i have so far to have the count of techs with cert1:

Techs with cert 1 = CALCULATE( COUNTROWS( 'certs' ), FILTER( 'certs', 'certs'[cert] = SELECTEDVALUE(calls[cert1])))

Thank you very much, any help will be precciated!

[Ashish_Mathur]

Hi,

How did you arrive at the numbers under the "#Techs with both certs" column in the last table?

[Anonymous]

Hi @Ashish_Mathur 

That´s esactly what im trying to get.

I can get the number of techs with cert1 or the number of techs with cert2 with this measures:

Techs with cert 1 = CALCULATE( COUNTROWS( 'certs' ), FILTER( 'certs', 'certs'[cert] = SELECTEDVALUE(calls[cert1])))

Techs with cert 2 = CALCULATE( COUNTROWS( 'certs' ), FILTER( 'certs', 'certs'[cert] = SELECTEDVALUE(calls[cert2])))

But i need the number of techs with both certs.

Regards

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

That's not my question.  I just do not understand how you arrived at those numbers from the input tables.

[Anonymous]

Hi,

@Ashish_Mathur 

just manually grouping and count it

regards