Forum Discussion
DAX COUNTX giving wrong results
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.
Super UserHi,
If you want to add up the values form the Data Table appearing against the bottom 5 ranking Year/Month, then try this measure
=Measure = SUMX(FILTER(SUMMARIZE(VALUES('Date Table'[Year and Month]),[Year and Month],"ABCD",[Rank],"EFGH",SUM(Data[Value])),[ABCD]<=5),[EFGH])
The answer is 308K
Hope this helps.
- Hi Ashish_Mathur,
Thank you for your reponse, though i am looking to count not sum the bottom 5 rankings Year/Month
The answer should be 5 instead of 24 but my code with countx keeps giving me 24 even if the condition i placed in the form of an if statement, tells it to only count the ranks ranked at 5 or below. Instead the code seems to carry out the count on every date hence the 24 result.
I guess what i do not understand is why my condition and code is not working- Ashish_Mathur
Hi,
Try this
Measure = COUNTOWS(FILTER(SUMMARIZE(VALUES('Date Table'[Year and Month]),[Year and Month],"ABCD",[Rank],"EFGH",SUM(Data[Value])),[ABCD]<=5))