Search with multiple criteria

[angelikakolacz]

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	INVOICE NUMBER	INVOICE AMOUNT	TYPE OF INVOICE	PERIODE	OUTPUT
1111	1234	€                       19,00	Inovice	3-2020	1236
1111	1235	€                      -19,00	Credit	7-2020	1238
1111	1236	€                      -19,00	Credit	3-2020	1234
1111	1237	€                      -19,00	Credit	8-2020	0
1111	1238	€                       19,00	Inovice	7-2020	1235
1111	1239	€                       19,00	Inovice	9-2020	0
1111	1240	€                       19,00	Inovice	6-2020	0
1111	1241	€                       19,00	Inovice	6-2020	0
1112	1242	€                       19,00	Inovice	7-2020	1244
1112	1243	€                       19,00	Inovice	8-2020	1245
1112	1244	€                      -19,00	Credit	7-2020	1242
1112	1245	€                      -19,00	Credit	8-2020	1243

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.

[angelikakolacz]

Hi @Jihwan_Kim , 

I get this as a result: 

A customer may receive more than 1 invoice for a specific periode. Could it be this? 

[Jihwan_Kim]

Hi,

Please share your sample pbix file's link here, and then I can try to look into it.

[angelikakolacz]

Hi @Jihwan_Kim 

Can you open my link?

[Jihwan_Kim]

Hi,

Thank you for your message, but I need a link to the pbix file, not a link to the Power BI service.

[angelikakolacz]

Hi @Jihwan_Kim ,

Can you explain how i can share a link with you? I cann't add a file..

[Jihwan_Kim]

HI,

Thank you for your message.

Instead of attaching a file, please try share the link of your pbix file.

Onedrive link, googledrive link, dropbox link, or any other link....

Thanks.

[angelikakolacz]

hi @Jihwan_Kim 

https://1drv.ms/u/s!AnJAHnTCiWB0hToVG88Z1rnJOyct?e=zeeiJn

[Jihwan_Kim]

Hi,

As seen in the below picture, some of them shows two invoice numbers. which invoice number do you want to select and show? Or, do you want to show two invoice numbers if there are two?

[angelikakolacz]

Hi @Jihwan_Kim 

Thank you for your answer. I want to see one unique value as output. 
So if the output is multiple output, i only want to see one unique value. If this one is already used as output i want to see another one (if there is another one).

Like this example: 

so as output i want to see one value by row. see this example: 
i have crossed out the unnecessary ones

[Jihwan_Kim]

Hi, Thank you for your information.

I am still a little bit confused. How to select which invoice number is used by which one? 

[angelikakolacz]

Hi @Jihwan_Kim 
If in the output a value is already used, it's not possible to see them again. in the output i don't want to see double values. Is it possible to use this like a criteria?

[Jihwan_Kim]

Hi,

Thank you for your information.

Sorry to bother you again, but what I meant was, 

in the below picture that you shared,

why first row takes 8100000003181511? why not 810000003181814?

How do you define the difference between the first row and the second row?

[angelikakolacz]

Hi @Jihwan_Kim 

It's only an example. it doesn't matter which one you take, if the value is unique (not already used before) it's oke.

[angelikakolacz]

Hi @Jihwan_Kim 

what I actually want to see is how many invoices are 0 (credit + debet) on balance if;

- it's the same account number,
- period it's the same

- sum of two invoices is 0.

But it is possible that in my file for a periode are 5 debet invoices and 4 credit invoices (same period). so 8 of them are right, but one is not good. In mine results i want to show that.

I must be able to show my results on row level, hence a new column with output.

[Jihwan_Kim]

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