cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Count of names based on condition

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

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Super User

@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
Not applicable

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?

Community Support

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