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

Frequent Visitor

## How to count the most recent value in a column when that value is 0

Hello,

I have spent days on this and cannot figure it out. I hope someone can help, please

I'm tracking skills so my model has skillnames, achieved levels, dates and persons name. (This is just a test - my actual table have millions of records but behaves the same)

Im using this measure to get the latest value of each skill for each person:

Latest result = var _maxDate = MAXX
(
FILTER
(
ALLSELECTED(Sheet1),
Sheet1[Skill name]= MAX(Sheet1[Skill name])
),
Sheet1[date])

return
CALCULATE
(
MAX(Sheet1[achieved level]),
FILTER
(
Sheet1,
Sheet1[Skill name]= MAX(Sheet1[Skill name]) && Sheet1[date] = _maxDate
)
)
It works and I can create this table

I am then using this measure to count up the number of each skill level:

num Level 4 = CALCULATE(
COUNTROWS(Sheet1),
FILTER(Sheet1,
Sheet1[Latest result] = 4
))
which gives the answer as 5 which is correct. This works for all the numbers except 0. the measure for 0 is the exact same:
num not started = CALCULATE(
COUNTROWS(Sheet1),
FILTER(Sheet1,
Sheet1[Latest result] = 0
))

but this gives me 13 which is the total number of 0's in the table not just those which are the latest value. Does anyone know why this is? If I change all my 0's to 10 for instance it works fine. So it is something related to the fact that I'm asking it to count the latest 0 values.
Any insight or help is most welcome.
Thank you.
1 ACCEPTED SOLUTION
Super User

@justlearning50 , try like

Countrows(filter(Sheet1, not(isblank([Latest result])) && [Latest result] =0))

2 REPLIES 2
Super User

@justlearning50 , try like

Countrows(filter(Sheet1, not(isblank([Latest result])) && [Latest result] =0))

Frequent Visitor

Thank you @amitchandak . That worked.

Why is this need for 0 only? is it a case that there is a default blank for the rows that are not the latest score, and the measure was reading these as 0? If I create a table with the achieved score and the latest score, I only see the rows with the latest score not all the rows with a blank.

Thank you again.

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.