Count of same job types

[Anonymous]

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. 

[amitchandak · Accepted solution]

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

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

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.

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]

Simple enough

[amitchandak · Accepted solution]

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

[Anonymous]

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?