Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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] )
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.
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
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.
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
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 still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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] )
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |