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
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.