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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
HB2104
Helper I
Helper I

DAX If Vlookup Statements

Hello -

 

I am trying to create a DAX that is a multiple if statement vlook up. The statement is if CoCd column is KHLT & USD than lookup in Bank info column for correct bank info (in that statement, answer is 54764. Any idea is Power Bi can do that in DAX? I am newer using it. Thanks! 

 

CoCdCurrencyG/LNew Column   CompanyCurrencyBank Info
KHLTUSD20000054764  LOMBGBP56765
KHLTUSD20000054764  LOMBUSD34356354
KHLTUSD20000054764  LOMBEUR & ALL OTHER46354636
KHLTGBP2000001241  KHLTUSD54764
KHLTGBP2000001241  KHLTEUR486785
LOMBEUR20000046354636  KHLTALL OTHER1241
LOMBEUR20000046354636  JDUSD3434534
LOMBEUR20000046354636  JDGBP45634
LOMBEUR20000046354636  JDCNH76572
LOMBUSD20000034356354  JDMAD23677
LOMBGBP20000056765  JDEUR & ALL OTHER3532
LOMBEUR20000046354636     
6 ACCEPTED SOLUTIONS
barritown
Super User
Super User

Hi @HB2104,

Try a calculated column like this:

barritown_0-1695667865037.png

And in plain text for convenience:

New Calculated Column = 
VAR _CurrentCoCd = [CoCd]
VAR _CurrentCurrency = [Currency]
RETURN COALESCE ( 
    MAXX ( FILTER ( Tbl2, [Company] = _CurrentCoCd && [Currency] = _CurrentCurrency ), [Bank Info] ),
    MAXX ( FILTER ( Tbl2, [Company] = _CurrentCoCd && CONTAINSSTRING ( [Currency], "ALL OTHER" ) ), [Bank Info] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

View solution in original post

HB2104
Helper I
Helper I

Thank you, awesome! I assume Tb12 in this is the table for the bank numbers?

View solution in original post

Yup, it's the second table with the columns [Company], [Currency] and [Bank Info]. In fact, it's "Tbl2", short for "Table 2". 🙂

View solution in original post

Thank you so much! Does it matter about the columns in these being "text" vs "number"? Like lets say I had the bank info and it wasn't just numbers it also had letters in it as well. 

View solution in original post

There shouldn't be any problems with text.

View solution in original post

Works great, thank you!

View solution in original post

14 REPLIES 14
barritown
Super User
Super User

Yup, more examples would make the task more clear I guess.

 

Am I correct that in fact you extract 8 days from the due date, not 7?

9/19/23 - 8 days = 9/11/23 => ok, Monday, let's process
9/15/23 - 8 days = 9/7/23 => not ok, Thursday => process on next available payday, which is 9/8/23

 

Should [SAP Pulls # of Business Days], [Bank Clears # of Business Days] be used in calculations?

Sure! Yes it's payment run day + 7 business working days that is correct.

 

SAP automated payment run days

Monday

Wednesday

Friday

SAP picks up documents due up to and including

Monday + 7 working days

Wednesday + 7 working days

Friday + 7 working days

Bank clearing day

Monday + 2 working days

Wednesday + 2 working days

Friday + 2 working days

 

Another example:

CoCdCurrencyNet Due DateDay of WeekCalc Date

JDSP     GPB             9/28/23            Thursday

Calendar Rules 

JDSP

 
 
 

SAP automated payment run days

Tuesday

Wednesday

 

SAP picks up documents due up to and including

Tuesday + 7 working days

Wednesday+ 7 working days

 

Bank clearing day

Tuesday + 2 working days

Wednesday+ 2 working days

 

 

In that example, the invoice would be picked up on the date Wednesday 9/20. 

 

I am not worried about the clearing aspect as I think I will be able to work that out if a calculation for the working days is possible. Thank you!! 

It's possible. For example, please check the file available here - 

https://www.dropbox.com/scl/fi/n4rgr2xtqb7t61dm1g58n/community-pdate.pbix?rlkey=ct90a1xacdlrpiy9cr2t...

 

This solution requires more testing but seems to work:

barritown_0-1696238067928.png

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

HB2104
Helper I
Helper I

Thank you, awesome! I assume Tb12 in this is the table for the bank numbers?

Yup, it's the second table with the columns [Company], [Currency] and [Bank Info]. In fact, it's "Tbl2", short for "Table 2". 🙂

Thank you so much! Does it matter about the columns in these being "text" vs "number"? Like lets say I had the bank info and it wasn't just numbers it also had letters in it as well. 

There shouldn't be any problems with text.

Works great, thank you!
barritown
Super User
Super User

Hi @HB2104,

Try a calculated column like this:

barritown_0-1695667865037.png

And in plain text for convenience:

New Calculated Column = 
VAR _CurrentCoCd = [CoCd]
VAR _CurrentCurrency = [Currency]
RETURN COALESCE ( 
    MAXX ( FILTER ( Tbl2, [Company] = _CurrentCoCd && [Currency] = _CurrentCurrency ), [Bank Info] ),
    MAXX ( FILTER ( Tbl2, [Company] = _CurrentCoCd && CONTAINSSTRING ( [Currency], "ALL OTHER" ) ), [Bank Info] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

 

I have a similar formula needed but this time I need to use date parameters and not sure if DAX can do that. Sample data:

CoCdCurrencyNet Due DateDay of WeekCalc Date
KHLTUSD9/19/23Tuesday?
LOMBEUR9/15/23Friday?

 

Calendar Rules

Company CodeSAP Payment Run DaySAP Pulls # of Business DaysBank Clears # of Business Days
KHLTMonday72
KHLTWednesday72
KHLTFriday72

 

So for the first example, on 9/19/23 due date will be pulled on Monday 9/11/23 (prior 7 business days) there is a payment run and that invoice is pulled so the column calc date would be 9/11/12. The second example would be date 9/8/23. No idea is DAX can take into account calculating dates based on those parameters. 

@HB2104,

Sorry, but I don't understand the logic here and the role of Calendar Rules in your calculations.

Why is there no Tuesday and LOMB in the second table? 

Could you please re-explain in more details how it should work?

 

The calendar rules are the work days that they do Accounts Payable payment runs, in this case they only run payments on Monday, Wednesday and Fridays. So if the due date is on 9/19/23, SAP will pick this invoice up Monday the 11th.

 

In that second example, the due date is 9/15/23 and 7 days prior to that would be 9/7/23 but that falls on a Thursday and SAP won't pick it up since it's not a payment run day. So the answer would be the following day, based on the calendar rules Friday the 8th it would be picked up for payment. Super complex and unsure a formula can be created for that logic. 😅

Are these Mon-Wed-Fri paydays applicable for all the companies? Otherwise, why are there no lines with "LOMB" in the second table?

LOMB has a payment run every day of the work week, I should have put that in there. There are a few others outside the sample I provided that are different such as just a payment run on Tuesdays.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors