Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
28 | |
12 | |
10 | |
10 | |
6 |