Advance your Data & AI career with 50 days of live learning, dataviz contests, hands-on challenges, study groups & certifications and more!
Get registeredGet Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now
I have daily stock data and I use a measure to calculate the daily range:
Daily Range =
SUMX (
'Daily',
ROUNDUP (
DIVIDE (
'Daily'[Close] - 'Daily'[Open],
'Daily'[Open],
BLANK()
),
2
)
)
Now I would like to count the number of occurences at each 2 decimal place. For this first I create a table:
Bins = GENERATESERIES ( -0.07, 0.06, 0.01 )
And then I use a measure to count the occurrences:
Range Count =
CALCULATE (
COUNTROWS ( 'Daily' ),
FILTER ( 'Daily', [Daily Range] = SELECTEDVALUE ( 'Bins'[Value], BLANK() ) )
)
However for some reason this doesn't work properly. I get impartial results:
As you can see I have plenty of historical values and if I create a calculated column instead, everything works perfectly. I need to do this via measures and prefarable not use Summarize function.
Update: I am attaching the PowerBI file for reference: PowerBI Report.pbix
This calculation should work but for some reason it doesn't want to work. Either I'm missing something very obvious or this has to do something with the misterious internal workings of DAX.
@zaza Highly likely decimal numbers mess up the result. I tried it like this,
Range Count =
CALCULATE (
COUNTROWS ( 'Daily' ),
FILTER ( 'Daily', INT( [Daily Range] * 100 ) = INT (SELECTEDVALUE ( 'Bins'[Value], BLANK() ) * 100) )
)
the result looks more reasonable then; but I haven't come up with a sophisticated solution for the moment.
PS: Unless for some special reason, I didn't see the point summing up daily percentage ([Daily Range]); I'd author the measure this way,
Daily Range =
VAR __open = MAX ( Daily[Open] )
VAR __close = MAX ( Daily[Close] )
RETURN
ROUNDUP ( DIVIDE ( __close - __open, __open, BLANK () ), 2 )
[Range Count] might be simplified as
COUNTROWS (
FILTER (
Daily, INT ( [Daily Range] * 100 ) = INT ( MAX ( 'Bins'[Value] ) * 100 )
)
)
| 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! |
@CNENFRNL Thanks this works, but as you pointed out this still gives back impartial results. There is no reason why the formulas shouldn't work. I also thought initially that this might be due to the decimals but that is why I round the numbers up. However it still doesn't work
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Check out the October 2025 Power BI update to learn about new features.
| User | Count |
|---|---|
| 8 | |
| 7 | |
| 6 | |
| 5 | |
| 4 |
| User | Count |
|---|---|
| 25 | |
| 8 | |
| 8 | |
| 8 | |
| 8 |