Forum Discussion

angelikakolacz's avatar
angelikakolacz
Icon for Helper I rankHelper I
3 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 NUMBERINVOICE NUMBER INVOICE AMOUNT TYPE OF INVOICEPERIODEOUTPUT
11111234 €                       19,00Inovice3-20201236
11111235 €                      -19,00Credit7-20201238
11111236 €                      -19,00Credit3-20201234
11111237 €                      -19,00Credit8-20200
11111238 €                       19,00Inovice7-20201235
11111239 €                       19,00Inovice9-20200
11111240 €                       19,00Inovice6-20200
11111241 €                       19,00Inovice6-20200
11121242 €                       19,00Inovice7-20201244
11121243 €                       19,00Inovice8-20201245
11121244 €                      -19,00Credit7-20201242
11121245 €                      -19,00Credit8-20201243

 

 

I am looking for the same (but opposite) invoice for the same period per customer. If this invoice does not appear, result 0 is sufficient.

 

I hope someone can help me with this.

Need Help

8 Replies

  • HoangHugo's avatar
    HoangHugo
    Icon for Solution Specialist rankSolution Specialist
    3 years ago

    Hi, try this

    Output = LOOKUPVALUE (Invocie column,Period column,EARLIER(Period column),Amount column,-EARLIER(Amount column),0)

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

      Hi HoangHugo 

      It doesn't work. I get this when i use your dax: 
      EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

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

    Hi, angelikakolacz 

     

    Your problem can be solved with a simple calculation column.

    Column = 
Var Output=CALCULATE (
    MAX ( 'Table'[INVOICE NUMBER] ),
    FILTER (
        'Table',
        [ACCOUNT NUMBER] = EARLIER ( 'Table'[ACCOUNT NUMBER] )
            && [PERIODE] = EARLIER ( 'Table'[PERIODE] )
            && [INVOICE AMOUNT] = - EARLIER ( 'Table'[INVOICE AMOUNT] )
    )
)
Return
IF(Output=BLANK(),0,Output)

     

    Considering that your quantity is relatively large, you can also use measure to solve this problem.

    Measure:

    Output = 
Var Output=CALCULATE (
    MAX ( 'Table'[INVOICE NUMBER] ),
    FILTER (
        ALL('Table'),
        [ACCOUNT NUMBER] = SELECTEDVALUE( 'Table'[ACCOUNT NUMBER] )
            && [PERIODE] = SELECTEDVALUE( 'Table'[PERIODE] )
            && [INVOICE AMOUNT] = - SELECTEDVALUE( 'Table'[INVOICE AMOUNT] )
    )
)
Return
IF(Output=BLANK(),0,Output)

    Hope that helps you.

     

    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.

    • angelikakolacz's avatar
      angelikakolacz
      Icon for Helper I rankHelper I
      3 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
        3 years ago

        Hi, angelikakolacz 

         

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

         

        Best Regards