cancel
Showing results for
Did you mean:
Post Patron

## 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, @Serdet

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

@Serdet , 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] )

Post Patron

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, @Serdet

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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors