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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

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
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.