Masure is not slicing in Table view

[aranthon]

I am using Power BI REST API to push delta data into dataset, the delta data push may add duplicate records into the dataset and API does not support data merge. So I am trying to handle the duplicate issue and figure out the way to de-duplicate records therefore I can consider only latest data for calcuations. 

To explore this, I created a sample table called Host in the push dataset. Please refer the below screen shot, I manually inserted some duplicate records and created a measure called “DuplicateFlagHost” which tags incremental number to each record in a table start from latest date, it works correctly.

Formulas:

DuplicateFlagHost = CALCULATE(COUNT(Host[Name]), FILTER(ALL(Host), Host[Ip] = Host[Ip] && Host[Name]>=MAX(Host[Name]) && Host[CreatedDate]>=MAX(Host[CreatedDate])),VALUES(Host[Ip]))

Tot_Latest = CALCULATE(COUNTROWS(Host),FILTER(Host,[DuplicateFlagHost]=1))

Tot_History = CALCULATE(COUNTROWS(Host),FILTER(Host,[DuplicateFlagHost]>1))

Two Score cards created using the filter DuplicateFlagHost=1 and 2 and they show exact count. Refer the highlights in the below screen, the metric values are sliced according to the value of filter DuplicateFlagHost.

Based on the outcome, I started to implement the same changes to one of my business table “AgentAvailability”, duplicate values are tagged correctly but I do not see any sliced value from the metric at row level. The score card also shows Blank.

Forumla

DuplicateFlag = CALCULATE(COUNT(AgentAvailability[RefreshDateValue]), FILTER(ALL(AgentAvailability), AgentAvailability[Id] = AgentAvailability[Id] && AgentAvailability[RefreshDateValue] >= MAX(AgentAvailability[RefreshDateValue])),VALUES(AgentAvailability[Id]))

LatestData = CALCULATE(COUNT(AgentAvailability[Id]),FILTER(AgentAvailability, AgentAvailability[DuplicateFlag]=1))

There is no much difference between these two tables except data but this logic works partially.

Please help me to figure out the cause for the issue.

[v-chuncz-msft]

@aranthon ,

You may try using RANKX.

https://community.powerbi.com/t5/Desktop/IF-AND-dax-Formula/m-p/293991#M129654

[aranthon]

Push dataset does not allow to add custom column except measure. So i can't use 'Earlier' function which can be used only in derived column rather measure.