cancel
Showing results 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

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
Solution Sage

@Kote101

try like this:

```Bottom Nth Value =
COUNTROWS(
FILTER(
ALL( 'Date Table' ),
RANKX(
ALL( 'Date Table' ),
[TotalValue], , DESC, Dense
) < 6
)
)```

Proud to be a Datanaut!

Solution Sage

@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 ()
)
)```

Proud to be a Datanaut!

11 REPLIES 11
Solution Sage

Hi @Kote101

can you post the raw data?

Proud to be a Datanaut!

Helper II

Sure, here are some images of the tables i used

Solution Sage

Hi @Kote101,

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

Proud to be a Datanaut!

Helper II

@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.

Solution Sage

@Kote101

try like this:

```Bottom Nth Value =
COUNTROWS(
FILTER(
ALL( 'Date Table' ),
RANKX(
ALL( 'Date Table' ),
[TotalValue], , DESC, Dense
) < 6
)
)```

Proud to be a Datanaut!

Helper II

Thanks @LivioLanzo, that worked thank you, do you have any idea why my original code was not working ?

Solution Sage

@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 ()
)
)```

Proud to be a Datanaut!

Helper II

@LivioLanzo Thank you !

Super User

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])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II
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
Super User

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

Announcements

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors