Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Count of names based on condition

Hi,

 

I have a dataset similar to the below

 

NameJobdate
JohnUnit rate01/01/2022
AlanUnit rate01/01/2022
JohnDay work01/01/2022
MarcDay work01/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

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

Screenshot 2022-01-19 142839.png

Best Regards,
Community Support Team _ Eason

 

View solution in original post

3 REPLIES 3
amitchandak
Super User
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.

 

Serdet_0-1642084303138.png

Any ideas as to why it is not working?

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

Screenshot 2022-01-19 142839.png

Best Regards,
Community Support Team _ Eason

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors