Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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.

8 REPLIES 8
v-zhangti
Community Support
Community Support

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)

vzhangti_0-1663235293639.png

 

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)

vzhangti_1-1663235505033.png

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.

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: 

angelikakolacz_0-1663241804885.png

 




 

 

Hi, @angelikakolacz 

 

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

 

Best Regards

Hi @v-zhangti 

 

Something like this: 

ACCOUNT NUMBERINVOICE NUMBERAMOUNTFACTUURTYPePERIODEOPENSTAANDOutput test 2
38281000009569743141INVjan-21081000009570262
38281000006224300141INVjan-21081000006224463
38281000009570262-141CMjan-21081000009569743
38281000006224463-141CMjan-21081000006224300

 

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

Hi, @angelikakolacz 

 

Add an index column to your data in Power Query.

vzhangti_0-1663739648364.png

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:

vzhangti_1-1663739779762.png

 

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.

 

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
Solution Specialist
Solution Specialist

Hi, try this

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.