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.

[v-zhangti]

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]

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]

Hi, @angelikakolacz 

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

Best Regards

[angelikakolacz]

Hi @v-zhangti 

Something like this: 

ACCOUNT NUMBER	INVOICE NUMBER	AMOUNT	FACTUURTYPe	PERIODE	OPENSTAAND	Output test 2
382	81000009569743	141	INV	jan-21	0	81000009570262
382	81000006224300	141	INV	jan-21	0	81000006224463
382	81000009570262	-141	CM	jan-21	0	81000009569743
382	81000006224463	-141	CM	jan-21	0	81000006224300

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]

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.

[angelikakolacz]

Hi @v-zhangti 

It's seems like it doesn't work at my file. When i try to make a retun column the file crashes. Will you try at my file?

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

[HoangHugo]

Hi, try this

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

[angelikakolacz]

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.