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.

15 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    Based on what I see in the sample, I tried to write DAX formula like below in order to create a calculated column.

    Please check the below picture and the attached pbix file.

     

     

    Output CC =
VAR _result =
    SUMMARIZE (
        FILTER (
            Data,
            Data[ACCOUNT NUMBER] = EARLIER ( Data[ACCOUNT NUMBER] )
                && Data[PERIODE] = EARLIER ( Data[PERIODE] )
                && Data[INVOICE AMOUNT]
                    = -1 * EARLIER ( Data[INVOICE AMOUNT] )
        ),
        Data[INVOICE NUMBER]
    )
RETURN
    _result + 0