Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need Help with a formula for duplicates

So I have a formula that I have attached below to assign a 1 or 0 if payments are a certain number. I realized I have more than one transaction for the same people that meet this critera, so I need a formula that will say if there is a 2nd transacation for this person that is <= -750 the 2nd transaction will be assigned 0. 

 

Is this possible for me to do?

 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ryan_mayu ,

    Thanks for the quick response. I have different information I'd like to add.

    Hi Anonymous ,

    Please refer to my pbix file.

    Sort by date or payment order first. Create a column.

    RANK =
RANKX (
    FILTER ( 'Table', 'Table'[customer] = EARLIER ( 'Table'[customer] ) ),
    'Table'[date],
    ,
    ASC,
    DENSE
)

    Then write dax based on your ideas.

    Measure =
IF (
    MAX ( 'Table'[total payments] ) <= -750
        && MAX ( 'Table'[RANK] ) = 1,
    1,
    IF ( MAX ( 'Table'[total payments] ) >= 0 && MAX ( 'Table'[RANK] ) > 1, 0, 0 )
)

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    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
      2 years ago

      Hi, I don't see a link to your pbix file.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

        It's here!

         

        Best Regards
        Community Support Team _ Rongtie

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

         

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    Anonymous 

    is this what you want?

     

    Column =
    VAR _total=sumx(FILTER('Table','Table'[people]=EARLIER('Table'[people])&&'Table'[date]<=EARLIER('Table'[date])),'Table'[transaction])
    return if (_total<-750,1,0)
    you can modify the coding in the return part
     
     
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    It would be easir to solve this using a combination of Power Query and DAX.  Share some data to work with and show the expected result.