cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Serdet
Helper V
Helper V

Count of same job types

Hi,

 

I have a dataset similar to the below.

 

Job IDWork TypeCost
1Day Work£10
1Unit Rate£100
2Day Work£70
2Unit Rate£60
3Day Work£200
4Day Work£400
5Unit 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
amitchandak
Super User
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] )

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

View solution in original post

v-janeyg-msft
Community Support
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])

vjaneygmsft_0-1643097507245.png

Below is my sample.

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

View solution in original post

4 REPLIES 4
v-janeyg-msft
Community Support
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])

vjaneygmsft_0-1643097507245.png

Below is my sample.

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

CNENFRNL
Super User
Super User

Simple enough

CNENFRNL_0-1642777712684.png

 


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!

amitchandak
Super User
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] )

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

Hi,

 

Thanks for your response. I have copied your DAX meausure and changed table names where applicable and are getting the below error message

 

Serdet_0-1642773157447.png

Any ideas?

 

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

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.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!