The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have this measure:
The issue I am having is how to perform calculations on the measure.
For example, I want a sum of all values < 11 but sumx does not work with boolean values.
Solved! Go to Solution.
@Qotsa try:
Less than 11 =
SUMX(
FILTER(
SUMMARIZE(
'powerbi114 view_finance',
'powerbi114 view_finance'[carer_id],
'powerbi114 view_finance'[Date]
)
[Daily Rest Decimal Hrs] < 11
)
,1
)
Thks again. Can you explain what is happening here?
SUMX( FILTER( SUMMARIZE( 'powerbi114 view_finance', 'powerbi114 view_finance'[carer_id], 'powerbi114 view_finance'[Date] )
SUMMARIZE( 'powerbi114 view_finance', 'powerbi114 view_finance'[carer_id], 'powerbi114 view_finance'[Date] )
This is the distinct combination of these 2 columns.
Check out:
https://dax.guide/summarizea
Ok. Thanks very much.
@Qotsa my pleasure 🙂
Don't forget to check out my showcase report - really, got some high level stuff there. Sure you will find there a lot of cool ideas.
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂
@Qotsa I think you are trying to do:
Less than 11 = SUMX(FILTER('powerbi114 view_finance',[Daily Rest Decimal Hrs] < 11),[Daily Rest Decimal Hrs])
@Qotsa oh, so do this:) :
Less than 11 = SUMX(FILTER('powerbi114 view_finance',[Daily Rest Decimal Hrs] < 11),1)
It's getting closer.
@Qotsa try:
Less than 11 =
SUMX(
FILTER(
SUMMARIZE(
'powerbi114 view_finance',
'powerbi114 view_finance'[carer_id],
'powerbi114 view_finance'[Date]
)
[Daily Rest Decimal Hrs] < 11
)
,1
)
That's fantastic. I'm getting the correct results for <11.
When I change it tho to =, it doesn't return the desired result. Any idea why?
@Qotsa I guess it's not exactly 32.26 🙂
Try to verify by doing
Less than 11 =
SUMX(
FILTER(
SUMMARIZE(
'powerbi114 view_finance',
'powerbi114 view_finance'[carer_id],
'powerbi114 view_finance'[Date]
)
[Daily Rest Decimal Hrs] > 32 && [Daily Rest Decimal Hrs] < 33
)
,1
)
Yep, that gives correct result.
@Qotsa my pleasure 🙂
Please don't forget to accept the previous message as a solution for community visibility.
P.S. Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas.
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
80 | |
78 | |
43 | |
37 |
User | Count |
---|---|
158 | |
111 | |
64 | |
59 | |
54 |