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

## Measure returning if the cumulative count value is max value or not

Hello

I would like to kindly ask you to help me with following problem.

I use a measure to count cumulative number of the measurements of unique product Id. I need it to filter only the first measurement of every product and remove all the next measurements (if exist) from visualization.

Below the output example:

The measure which I use:

``````CummulativeCount =
CALCULATE (
COUNT (Append1[Id]),
FILTER (
ALLSELECTED (Append1),
Append1[Id] = SELECTEDVALUE (Append1[Id])
&& Append1[DateTime] <= SELECTEDVALUE(Append1[DateTime])
)
)``````

And this work totally OK- I am able to easy filter only the first measurement of every product Id.

But right now I need also a second measure (or other solution), which will help me to filter only the last measurement of every product Id. So for one product Id it can be the 1st measurement, but for another product Id it can be a f.e. 4th measurement.

So can anyone please help me with a measure solution (or other solution) which return if the cumulative count for every unique product Id is the max value or not?

Below an output example, which would fit perfect for me. In column MaxCumulativeCount the "1" means last measurement and the "0" means not last measurement.

Any help is appreciated

1 ACCEPTED SOLUTION
Frequent Visitor

Hello together

Thank you for all proposed solutions! Unfortunately I could not receive required result using your proposals. The measures were assigning the same value - last or blank - to all records.

But nevertheless I found a working solution. I use the solution of belisqui  from link to create calculated column.

``````LastMeasurement =

IF (
ALL_AKC[DateTime] = CALCULATE( MAX(ALL_AKC[DateTime]), FILTER(ALL_AKC, ALL_AKC[Id] = EARLIER(ALL_AKC[Id])))
,true()
,false()
)``````

All last measurement of every unique ID are set as true.

Thank you all again for support!

7 REPLIES 7
Frequent Visitor

Hello together

Thank you for all proposed solutions! Unfortunately I could not receive required result using your proposals. The measures were assigning the same value - last or blank - to all records.

But nevertheless I found a working solution. I use the solution of belisqui  from link to create calculated column.

``````LastMeasurement =

IF (
ALL_AKC[DateTime] = CALCULATE( MAX(ALL_AKC[DateTime]), FILTER(ALL_AKC, ALL_AKC[Id] = EARLIER(ALL_AKC[Id])))
,true()
,false()
)``````

All last measurement of every unique ID are set as true.

Thank you all again for support!

Memorable Member

Hi,
It might not be the best solution, but you could test as you did for your cumultive count, something like :

`VAR LastDateOfID = MAX or LASTDATE( YourDate ) RETURNIF ( YourDate = LastDateOfID , "last one" , BLANK() )`

Hope it helps, let us know

Frequent Visitor

Hello

first for all thank you for the support!

Unfortunately the proposed solution does not seem to work.

I tried to put it in various combinations, but there is always some error.

Example below:

If 'Max or' will be removed than the same error is for RETURN etc.

I hope I also past the correct column from my data set - which is a date/time column -  in the column described by you as 'YourDate'.

If I did something wrong please let me know.

Anonymous
Not applicable

Try SelectedValue('Table'[Date]) = Max('Table'[Date]) or LastDate('Table'[Date]). Because Max () or LastDate () returns a singular value.

Super User
`VAR LastDateOfID = MAX(YourDate) `

OR

`VAR LastDateOfID = LASTDATE(YourDate) `

Proud to be a Super User!

Frequent Visitor

Hello

I checked it before as well. Unfortunately it does not work as well. The RETURN error appears.

Memorable Member

Hi,

Those are 2 neew measures you should create and you should like for all meausres gives them a name. It should begin with the name:

NameOfMeasure = LASTDATE....

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.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors