cancel
Showing results for
Did you mean:
Helper I

## 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 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.

8 REPLIES 8
Community Support

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.

Helper I

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:

Community Support

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

Best Regards

Helper I

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

Community Support

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.

Helper I

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

Solution Specialist

Hi, try this

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

Helper I

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.