Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
You may try using RANKX.
https://community.powerbi.com/t5/Desktop/IF-AND-dax-Formula/m-p/293991#M129654
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |