topic Re: Search with multiple criteria in DAX Commands and Tips

Search with multiple criteria

angelikakolacz — Wed, 14 Sep 2022 08:09:20 GMT

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.

Re: Search with multiple criteria

Jihwan_Kim — Wed, 14 Sep 2022 09:15:08 GMT

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

Re: Search with multiple criteria

angelikakolacz — Wed, 14 Sep 2022 11:22:58 GMT

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?

Re: Search with multiple criteria

Jihwan_Kim — Wed, 14 Sep 2022 13:55:26 GMT

Hi,

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

Re: Search with multiple criteria

angelikakolacz — Thu, 15 Sep 2022 11:45:06 GMT

Hi Jihwan_Kim

Can you open my link?

Re: Search with multiple criteria

Jihwan_Kim — Thu, 15 Sep 2022 11:08:44 GMT

Hi,

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

Re: Search with multiple criteria

angelikakolacz — Wed, 21 Sep 2022 12:10:01 GMT

Hi Jihwan_Kim ,

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

Re: Search with multiple criteria

Jihwan_Kim — Wed, 21 Sep 2022 14:11:46 GMT

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.

Re: Search with multiple criteria

angelikakolacz — Thu, 22 Sep 2022 07:20:11 GMT

hi Jihwan_Kim

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

Re: Search with multiple criteria

Jihwan_Kim — Thu, 22 Sep 2022 07:41:19 GMT

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?

Re: Search with multiple criteria

angelikakolacz — Thu, 22 Sep 2022 09:18:21 GMT

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

Re: Search with multiple criteria

Jihwan_Kim — Thu, 22 Sep 2022 10:03:43 GMT

Hi, Thank you for your information.

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

Re: Search with multiple criteria

angelikakolacz — Thu, 22 Sep 2022 11:42:11 GMT

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?

Re: Search with multiple criteria

Jihwan_Kim — Thu, 22 Sep 2022 11:51:08 GMT

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?

Re: Search with multiple criteria

angelikakolacz — Thu, 22 Sep 2022 12:22:06 GMT

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.

Re: Search with multiple criteria

angelikakolacz — Thu, 22 Sep 2022 14:28:59 GMT

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.