Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
zaza
Resolver III
Resolver III

Counting rows based on a row wise measure via an unrelated table

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:

Screen Shot 2020-09-12 at 3.56.13 PM.png

 

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.

3 REPLIES 3
zaza
Resolver III
Resolver III

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.

CNENFRNL
Community Champion
Community Champion

@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.

Screenshot 2020-09-12 202148.png 

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

Helpful resources

Announcements
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.