Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
(Heres an image of my dashboard; instead of counting the right number of dates at 5 or under , it counts every date)
Bottom 5 = VAR N = 5 RETURN COUNTX( SUMMARIZE ( 'Date Table','Date Table'[Year and Month],"total" , RANKX(all('Date Table'[Year and Month]), CALCULATE(SUM(Data[Value])),,ASC,Dense)) , IF([total]<=N,[total],BLANK()))
Solved! Go to Solution.
try like this:
Bottom Nth Value = COUNTROWS( FILTER( ALL( 'Date Table' ), RANKX( ALL( 'Date Table' ), [TotalValue], , DESC, Dense ) < 6 ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 () ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Kote101
can you post the raw data?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Sure, here are some images of the tables i used
Hi @Kote101,
sorry but I can't work with a snapshot. Are you able to post data which can be copy/pasted?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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.
try like this:
Bottom Nth Value = COUNTROWS( FILTER( ALL( 'Date Table' ), RANKX( ALL( 'Date Table' ), [TotalValue], , DESC, Dense ) < 6 ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks @LivioLanzo, that worked thank you, do you have any idea why my original code was not working ?
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 () ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi,
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,
Try this
Measure = COUNTOWS(FILTER(SUMMARIZE(VALUES('Date Table'[Year and Month]),[Year and Month],"ABCD",[Rank],"EFGH",SUM(Data[Value])),[ABCD]<=5))
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |