Count of names based on condition

[Anonymous]

Hi,

I have a dataset similar to the below

Name	Job	date
John	Unit rate	01/01/2022
Alan	Unit rate	01/01/2022
John	Day work	01/01/2022
Marc	Day work	01/01/2022

I want to calculate a count of employees who have worked jobs on unit rate and day work in the same day.

The caluclation based on the above dataset will give me a count of 1 because John has worked a unit rate job and a day work job on the same day.

Any ideas how I can calculate this with DAX?

Many thanks

[amitchandak]

@Anonymous , Plot the measure with name

countx(filter(summarize(Table, Table[Name], "_1", countx(filter(Table, Table[Job] = "Unit rate"),[Job])
, "_2", countx(filter(Table, Table[Job] = " Day work"),[Job]) )
not(isblank(_1)) && not(isblank(_2)) && [_1] >0 && [_2]>0), [Name] )

[Anonymous]

Thanks for the speedy response.

I have copied the DAX (please note, some column names are different in my real dataset.

I am experiencing the below seen errors.

Any ideas as to why it is not working?

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

You can try measure as below:

Measure1 = 
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Name],
        'Table'[date],
        "countjob", DISTINCTCOUNT ( 'Table'[Job] )
    )
RETURN
    COUNTROWS ( FILTER ( tab, [countjob] > 1 ) )

Best Regards,
Community Support Team _ Eason