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.
My data looks like the following:
ID Charge_Code Amount
1001 Opening 1100
1001 Opening 110
1001 Purchase 500
1002 Opening 2000
1002 Purchase 700
1003 Opening 1500
1004 Opening 170
1004 Opening 1700
Some of my data has been incorrectly entered. For example IDs 1001 and 1004 have two account opening entries when they should only have one. This is because someone incorrectly entered the first value (highlighted in red) and then entered the second value correctly.
I want my data to look like the following:
ID Charge_Code Amount
1001 Opening 110
1001 Purchase 500
1002 Opening 2000
1002 Purchase 700
1003 Opening 1500
1004 Opening 1700
The above table has deleted the IDs with duplicate 'Opening' Charge_Code and kept the latest entry. My data does not have a time stamp but the order of the rows in default is chronological.
Please help!
Solved! Go to Solution.
Hi @danialsj ,
Assume that the second duplicate value is correct.
Try this:
Measure 2 =
VAR Duplicate =
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Charge_Code] )
) <> 1,
1,
0
)
VAR index_1 =
MAX ( 'Table'[Index] ) + 1
VAR Measure_ =
CALCULATE (
DISTINCT ( 'Table'[Amount] ),
index_1 = 'Table'[Index],
ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Charge_Code] )
)
RETURN
IF ( Duplicate = 0, MAX ( 'Table'[Amount] ), Measure_ )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @danialsj ,
Assume that the second duplicate value is correct.
Try this:
Measure 2 =
VAR Duplicate =
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Charge_Code] )
) <> 1,
1,
0
)
VAR index_1 =
MAX ( 'Table'[Index] ) + 1
VAR Measure_ =
CALCULATE (
DISTINCT ( 'Table'[Amount] ),
index_1 = 'Table'[Index],
ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Charge_Code] )
)
RETURN
IF ( Duplicate = 0, MAX ( 'Table'[Amount] ), Measure_ )
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @danialsj Does your table have some kind of a timestamp to determine which among the row by ID is the latest? Supposing you have it or any other indicator columns, your DAX column formula should be something like this:
Latest =
VAR __LATEST =
CALCULATE ( MAX ( 'Table'[Timestamp] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
IF (
'Table'[Chage_Code] <> "Opening"
|| 'Table'[Timestamp] = __LATEST,
"Yes",
"No"
)
Proud to be a Super User!
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 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |