Forum Discussion
Identify duplicate supplier using measure
Se the 2 tables I have with a relation with Vendor ID
| Vendor | TAX | |||
| Vendor ID | Available For use | Vendor ID | Tax | |
| 1 | Yes | 1 | A | |
| 2 | Yes | 2 | B | |
| 3 | No | 3 | C | |
| 4 | No | 4 | A | |
| 5 | Yes | 5 | B | |
| 6 | No | 6 | D |
I need to acheive the below table :
| Vendor ID | From Table Vendor |
| Duplicate | Yes/No - If the tax of the Vendor ID is duplicate with another Vendor ID |
| Duplicate Group | Assign a unique ID for the group of duplicate |
| Duplicate List | List all the Vendor ID assigned in the same group |
The challenge I have is that if I do it in Power Query or in the Table with New Colmun, the result will not be dynamic.
See the Avaialble for Use as a FIlter.
If the end user select only the Available For Use to Yes, the system must calculte the field Duplicate, Duplicate Group & Dupicate List only with the Vendor filtered to Yes.
I therefore need to create some calculated table, or summarize inside a measure to make sure it would work on a dinamic way.
Any idea how to acheive this ?
Hi Martin0011
You can create several measures as follow.
Duplicate = IF(CALCULATE(COUNT('TAX'[Tax]),'TAX'[Tax] = MAX('TAX'[Tax]), ALL('Vendor'[Vendor ID])) > 1, "Y", "N")Duplicate List = IF([Duplicate] = "Y", CONCATENATEX(FILTER(ALLSELECTED('TAX'), 'TAX'[Tax] = MAX('TAX'[Tax])), 'TAX'[Vendor ID], ","))Duplicate Group = IF ( [Duplicate] = "Y", RANKX ( FILTER ( ADDCOLUMNS ( ALLSELECTED ( 'Vendor'[Vendor ID] ), "@Duolicate", [Duplicate], "@DuplicateList", [Duplicate List] ), [@Duolicate] = "Y" ), [Duplicate List], , ASC, DENSE ), "-" )Is this the result you expect?
Best Regards,
Community Support Team _YuliaxIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
So I had to break down your problem in 3 measures :
Duplicate = VAR currentVendorID = SELECTEDVALUE('Vendor'[Vendor ID]) VAR currentTax = LOOKUPVALUE('TAX'[Tax], 'TAX'[Vendor ID], currentVendorID) RETURN IF( CALCULATE( COUNTROWS('Vendor'), FILTER( ALL('Vendor'), 'Vendor'[Vendor ID] <> currentVendorID && 'Vendor'[Available For Use] = "Yes" && LOOKUPVALUE('TAX'[Tax], 'TAX'[Vendor ID], 'Vendor'[Vendor ID]) = currentTax ) ) > 0, "Yes", "No" )Duplicate Group = VAR currentTax = LOOKUPVALUE('TAX'[Tax], 'TAX'[Vendor ID], SELECTEDVALUE('Vendor'[Vendor ID])) RETURN RANKX( ALL('TAX'), currentTax, , ASC, Dense )This is the output :
I think you missed the 3rd measure. In your approach, they all have the same duplicate group and the duplicate list is only the same as the vendor id.
The output result should be :If the Filter Available for use is ALL Vendor ID Duplicate Duplicate Group Duplicate List 1 Y 1 1,4 2 Y 2 2,5 3 N - 4 Y 1 1,4 5 Y 2 2,5 6 N - If the Filter Available for use is YTE Vendor ID Duplicate Duplicate Group Duplicate List 1 N - 2 Y 1 2,5 5 Y 1 2,5 Can you please provide a clear input and output ?
Hi Martin0011
You can create several measures as follow.
Duplicate = IF(CALCULATE(COUNT('TAX'[Tax]),'TAX'[Tax] = MAX('TAX'[Tax]), ALL('Vendor'[Vendor ID])) > 1, "Y", "N")Duplicate List = IF([Duplicate] = "Y", CONCATENATEX(FILTER(ALLSELECTED('TAX'), 'TAX'[Tax] = MAX('TAX'[Tax])), 'TAX'[Vendor ID], ","))Duplicate Group = IF ( [Duplicate] = "Y", RANKX ( FILTER ( ADDCOLUMNS ( ALLSELECTED ( 'Vendor'[Vendor ID] ), "@Duolicate", [Duplicate], "@DuplicateList", [Duplicate List] ), [@Duolicate] = "Y" ), [Duplicate List], , ASC, DENSE ), "-" )Is this the result you expect?
Best Regards,
Community Support Team _YuliaxIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
