Forum Discussion
DAX COUNTX giving wrong results
Hi, I am trying to get my code to count the number of dates that have a rank or 5 or anything below 5. I tried to do this with the code below but it doesn't seem to work, could any one help please? ...
Anonymous
try like this:
Bottom Nth Value = COUNTROWS( FILTER( ALL( 'Date Table' ), RANKX( ALL( 'Date Table' ), [TotalValue], , DESC, Dense ) < 6 ) )Anonymous
For your measure to work you need to remove the filter from 'Year and Month' and from 'Date' such as below:
Bottom 5 = VAR N = 5 RETURN COUNTX ( SUMMARIZE ( 'Date Table', 'Date Table'[Year and Month], "total", RANKX ( ALL ( 'Date Table'[Year and Month], 'Date Table'[Date] ), CALCULATE ( SUM ( Data[Value] ) ), , ASC, DENSE ) ), IF ( [total] <= N, [total], BLANK () ) )
Sorry i think this may help more;
https://filebin.net/qlgusdv2f3w0pfm4
you can access the pbix file i am working on there and the file i am using too.
LivioLanzo
Solution Sage
Solution SageAnonymous
try like this:
Bottom Nth Value =
COUNTROWS(
FILTER(
ALL( 'Date Table' ),
RANKX(
ALL( 'Date Table' ),
[TotalValue], , DESC, Dense
) < 6
)
)Thanks LivioLanzo, that worked thank you, do you have any idea why my original code was not working ?
- LivioLanzo
Anonymous
For your measure to work you need to remove the filter from 'Year and Month' and from 'Date' such as below:
Bottom 5 = VAR N = 5 RETURN COUNTX ( SUMMARIZE ( 'Date Table', 'Date Table'[Year and Month], "total", RANKX ( ALL ( 'Date Table'[Year and Month], 'Date Table'[Date] ), CALCULATE ( SUM ( Data[Value] ) ), , ASC, DENSE ) ), IF ( [total] <= N, [total], BLANK () ) )LivioLanzo Thank you !