cancel
Showing results for
Did you mean:  Helper V

## Count of same job types

Hi,

I have a dataset similar to the below.

 Job ID Work Type Cost 1 Day Work £10 1 Unit Rate £100 2 Day Work £70 2 Unit Rate £60 3 Day Work £200 4 Day Work £400 5 Unit Rate £100

Can a DAX measure be created to do the following

1) Count Job ID's that have unit rate and day work associated with them? For example, in the above dataset, Job ID 1 & 2 both have Unit Rate and Day Work therefore the measure would return a count of 2

2) Where Job ID's have unit rate and day work associated with them, SUM the Cost? For example, in the above dataset, Job ID 1 & 2 have Unit Rate and Day Work therefore the measure would return a Cost sum of £240

Hope this makes sense.

2 ACCEPTED SOLUTIONS  Super User

@Serdet , Try two measures like

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

Sumx(filter(summarize(Table, Table[Name], "_1", countx(filter(Table, Table[ Unit Rate or Day Work] = "Unit rate"),[Job])
, "_2", countx(filter(Table, Table[ Unit Rate or Day Work] = " Day work"),[Job]), "_Sum", sum(Table[Cost]) )
not(isblank(_1)) && not(isblank(_2)) && [_1] >0 && [_2]>0), [Cost] )

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!  Community Support

Hi, @Serdet

I created a flag column and two measures to meet your needs.

Like this:

``Flag = COUNTX(FILTER('Table',([Work Type]="Day Work"||[Work Type]="Unit Rate")&&[Job ID]=EARLIER('Table'[Job ID])),[Job ID])``
``Measure1 = CALCULATE( DISTINCTCOUNT('Table'[Job ID]),'Table'[Flag]=2)``
``Measure2 = SUMX(FILTER('Table',[Flag]=2),[Cost])`` Below is my sample.

Best Regards,
Community Support Team _ Janey

4 REPLIES 4  Community Support

Hi, @Serdet

I created a flag column and two measures to meet your needs.

Like this:

``Flag = COUNTX(FILTER('Table',([Work Type]="Day Work"||[Work Type]="Unit Rate")&&[Job ID]=EARLIER('Table'[Job ID])),[Job ID])``
``Measure1 = CALCULATE( DISTINCTCOUNT('Table'[Job ID]),'Table'[Flag]=2)``
``Measure2 = SUMX(FILTER('Table',[Flag]=2),[Cost])`` Below is my sample.

Best Regards,
Community Support Team _ Janey  Super User

Simple enough Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension! DAX is simple, but NOT EASY!  Super User

@Serdet , Try two measures like

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

Sumx(filter(summarize(Table, Table[Name], "_1", countx(filter(Table, Table[ Unit Rate or Day Work] = "Unit rate"),[Job])
, "_2", countx(filter(Table, Table[ Unit Rate or Day Work] = " Day work"),[Job]), "_Sum", sum(Table[Cost]) )
not(isblank(_1)) && not(isblank(_2)) && [_1] >0 && [_2]>0), [Cost] )

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!  Helper V

Hi,

Thanks for your response. I have copied your DAX meausure and changed table names where applicable and are getting the below error message Any ideas? Announcements #### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world. #### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st! #### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better. Top Solution Authors
Top Kudoed Authors
Users online (1,622)