Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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? ...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Anonymous

     

    try like this:

     

    Bottom Nth Value = 
COUNTROWS(
    FILTER(
        ALL( 'Date Table' ),
        RANKX(
            ALL( 'Date Table' ),
            [TotalValue], , DESC, Dense
        ) < 6
    )
)
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    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's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
7 years ago

Hi Anonymous

 

can you post the raw data?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Sure, here are some images of the tables i used 

  • LivioLanzo's avatar
    LivioLanzo
    Icon for Solution Sage rankSolution Sage
    7 years ago

    Hi Anonymous,

     

    sorry but I can't work with a snapshot. Are you able to post data which can be copy/pasted?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        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.