Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Duplicate search in table

Trying to find a solution for this below requirement

 

Have a Purchasing table with the purchase details. Need to identify the duplicates based on the criteria 

-  Vendor and Planned recept date is in 5 days of the selected date( Slicer value )  and amount < = 1500

 

VendorPurch OrdPlanned Rcpt DateAmount
10200013/7/20231500
20200023/7/2023100
10200037/7/20231400
10200047/7/20231700
202000714/7/20231100
252000816/7/20231000

 

Expected result -  if the selected date is 03/07/2023, these 2 transactions are what is needed in the report 

 

VendorPurch OrdPlanned Rcpt DateAmount
10200013/7/20231500
10200037/7/20231400

 

Pbix attached  - 

https://drive.google.com/file/d/1DELCr4EhRnJDPOyJY4BN2USLRMjrn3y7/view?usp=sharing

 

Need Help

4 Replies

  • gregoliveira's avatar
    gregoliveira
    Icon for Helper II rankHelper II
    3 years ago

    Hi.

    Can you explain me a little bit more? Are you having trouble to author the measure to identify the rows or to filter?

     

    After you author and measure that you identify the rows, you can use it in the filter painel to show only the desired rows.

     

    Hope this help you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      gregoliveira 

       

      Yes. How can the measure be constructed to tie the duplicate transactions together based on the criteria  and show them in the visual. 

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Anonymous ,

     

    Here are the steps you can follow：

    1. Create calculated table.

     

    Table =
CALENDAR(
    DATE(
        2023,1,1),
        DATE(
            2023,12,31))

     

    2. Create measure.

     

    Flag =
var _select=SELECTEDVALUE('Table'[Date])
var _count=
COUNTX(
    FILTER(ALL(Purchase),
    'Purchase'[Planned Rcpt Date] <= _select +5&&'Purchase'[Vendor]=MAX('Purchase'[Vendor])&&MAX('Purchase'[Amount])<=1500),[Purch Ord])
return
IF(
    _count>=2,1,0)

     

    3. Place [Flag]in Filters, set is=1, apply filter.

    4. Result:

     

     

    Best Regards,

    Liu Yang

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Anonymous 

       

      This gave a mixed result. Thanks for sharing. 

       

      Here is what i see. The date selection seem to be ignored. I tried setting a relationship between dates and that picked only the transaction for the selected date. Made relation inactive and this provides the transaction prior to the date selected too. Attaching my pbix. Sorry not sure what messed up. . 

       

      https://drive.google.com/file/d/1DELCr4EhRnJDPOyJY4BN2USLRMjrn3y7/view?usp=sharing