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

[karolp]

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

[karolp · Accepted solution]

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

The received result:

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

Thank you all again for support!

[AilleryO]

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 ) 
RETURN
IF ( YourDate = LastDateOfID , "last one" , BLANK() )

Hope it helps, let us know

[karolp]

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]

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

[bolfri]

VAR LastDateOfID = MAX(YourDate) 

OR

VAR LastDateOfID = LASTDATE(YourDate) 

[karolp]

Hello

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

[AilleryO]

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