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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.