Forum Discussion

Martin0011's avatar
Martin0011
Frequent Visitor
2 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 _Yuliax

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

AmiraBedh's avatar
AmiraBedh
Super User
2 years ago

Can you please provide a clear input and output ?

Martin0011's avatar
Martin0011
Frequent Visitor
2 years ago

Input it this : 2 tables 

Vendor TAX
Vendor IDAvailable For use Vendor IDTax
1Yes 1A
2Yes 2B
3No 3C
4No 4A
5Yes 5B
6No 6D

 

Ouputs : 

If the Filter Available for use is ALL 
    
Vendor IDDuplicateDuplicate GroupDuplicate List
1Y11,4
2Y22,5
3N- 
4Y11,4
5Y22,5
6N- 
    
    
If the Filter Available for use is YES 
    
Vendor IDDuplicateDuplicate GroupDuplicate List
1N- 
2Y12,5
5Y12,5

 

The Duplicate measure should return Yes if (after any filter set up) thare a multiple vendor with the same tax. 
The duplicate Group measure should assign the same number to all supplier (after any filter set up) that have the same Tax.
The Duplicate List measure should list all the supplier that are assigned to a group.