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

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 : 

 

 

 

 

Martin0011's avatar
Martin0011
Frequent Visitor
2 years ago

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 IDDuplicateDuplicate GroupDuplicate List
1Y11,4
2Y22,5
3N- 
4Y11,4
5Y22,5
6N- 
    
    
If the Filter Available for use is YTE 
    
Vendor IDDuplicateDuplicate GroupDuplicate List
1N- 
2Y12,5
5Y12,5
  • 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.