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

Get 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

Reply
Kote101
Helper II
Helper II

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

 

 

2 ACCEPTED SOLUTIONS

@Kote101

 

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!  

View solution in original post

@Kote101

 

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!  

View solution in original post

11 REPLIES 11
LivioLanzo
Solution Sage
Solution Sage

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!  

@LivioLanzo

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.

@Kote101

 

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 ?

@Kote101

 

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!  

@LivioLanzo Thank you ! 

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

Try this

 

Measure = COUNTOWS(FILTER(SUMMARIZE(VALUES('Date Table'[Year and Month]),[Year and Month],"ABCD",[Rank],"EFGH",SUM(Data[Value])),[ABCD]<=5))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.