topic Re: Counting rows based on a row wise measure via an unrelated table in DAX Commands and Tips

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

zaza — Sat, 12 Sep 2020 14:07:25 GMT

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.

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

zaza — Sat, 12 Sep 2020 16:54:42 GMT

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.

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

CNENFRNL — Sat, 12 Sep 2020 18:38:54 GMT

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 ) ) )

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

zaza — Sun, 13 Sep 2020 07:43:34 GMT

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