Forum Discussion

angelikakolacz's avatar
angelikakolacz
Icon for Helper I rankHelper I
4 years ago

search with multiple criteria

Hello, I have a table with more than milion rows with data from different customers. I’m looking for a dax formule that gives me the following result (see column output):   ACCOUNT NUMBER INVO...
Need Help
angelikakolacz's avatar
angelikakolacz
Icon for Helper I rankHelper I
4 years ago

Hi v-zhangti , 

Thank you for your help. We are almost there. 

When i used your calculation, i get double values as output. The values needs to be unique and if there is no another unique value anymore than 0. 
See below result for an example: 

 




 

 

v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
4 years ago

Hi, angelikakolacz 

 

Like the example you provided, what are the results you expect? Because his result is one-on-two.

 

Best Regards

  • angelikakolacz's avatar
    angelikakolacz
    Icon for Helper I rankHelper I
    4 years ago

    Hi v-zhangti 

     

    Something like this: 

    ACCOUNT NUMBERINVOICE NUMBERAMOUNTFACTUURTYPePERIODEOPENSTAANDOutput test 2
    38281000009569743141INVjan-21081000009570262
    38281000006224300141INVjan-21081000006224463
    38281000009570262-141CMjan-21081000009569743
    38281000006224463-141CMjan-21081000006224300

     

    If one value is already used, it's not possible to use them twice. If there is not an other value, then 0.

     

    Best regards

    • v-zhangti's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi, angelikakolacz 

       

      Add an index column to your data in Power Query.

      Column:

      Rank = 
CALCULATE (
    COUNT ( 'Table'[ACCOUNT NUMBER] ),
    FILTER (
        'Table',
        [Index] <= EARLIER ( 'Table'[Index] )
            && [AMOUNT] = EARLIER ( 'Table'[AMOUNT] )
            && [PERIODE] = EARLIER ( 'Table'[PERIODE] )
    )
)
      Column =
CALCULATE (
    MAX ( 'Table'[INVOICE NUMBER] ),
    FILTER (
        'Table',
        [ACCOUNT NUMBER] = EARLIER ( 'Table'[ACCOUNT NUMBER] )
            && [PERIODE] = EARLIER ( 'Table'[PERIODE] )
            && [AMOUNT] = - EARLIER ( 'Table'[AMOUNT] )
            && [Rank] = EARLIER ( 'Table'[Rank] )
    )
)

      Result:

       

      Best Regards,

      Community Support Team _Charlotte

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