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.
Hi,
I'm trying to come up with a formula that compares two sets of data in the same table. For example here is some sample data:
Identifier1,Identifier2,Concatenate,Date,Unique ID
8.8.8.8,1234,8.8.8.81234,1/12/20,1
8.8.8.8,4321,8.8.8.84321,1/12/20,1
1.2.3.4,1234,1.2.3.41234,1/12/20,1
1.2.3.4,4321,1.2.3.44321,1/12/20,1
8.8.8.8,1234,8.8.8.81234,1/20/20,2
8.8.8.8,4321,8.8.8.84321,1/20/20,2
8.8.8.8,1234,8.8.8.81234,1/30/20,3
8.8.8.8,4321,8.8.8.84321,1/30/20,3
1.2.3.4,1234,1.2.3.41234,1/30/20,3
1.2.3.4,4321,1.2.3.44321,1/30/20,3
4.3.2.1,1234,4.3.2.11234,1/30/20,3
What I want to do I check and see if any of the concatenated values are duplicates but only from the previous date (Unique ID -1). Let me know if this makes sense.
I want to do this with Power BI bc I will be adding new files to the data every week, so my hope is that it will automatically add that calculations between weeks to see what's duplicate this week from last week, as well as what is new.
Solved! Go to Solution.
So I am thinking perhaps something like the following:
Column Duplicate =
VAR __UniqueID = 'Table'[UniqueID]
VAR __Table =
SELECTCOLUMNS(
FILTER('Table','Table'[UniqueID] = __UniqueID - 1),
"__Concatenate",'Table'[Concatenate]
)
VAR __Current = 'Table'[Concatenate]
RETURN
IF(__Current IN __Table,"Yes","No)
So I am thinking perhaps something like the following:
Column Duplicate =
VAR __UniqueID = 'Table'[UniqueID]
VAR __Table =
SELECTCOLUMNS(
FILTER('Table','Table'[UniqueID] = __UniqueID - 1),
"__Concatenate",'Table'[Concatenate]
)
VAR __Current = 'Table'[Concatenate]
RETURN
IF(__Current IN __Table,"Yes","No)
So basically:
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |