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
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!
CoCd | Currency | G/L | New Column | Company | Currency | Bank Info | ||
KHLT | USD | 200000 | 54764 | LOMB | GBP | 56765 | ||
KHLT | USD | 200000 | 54764 | LOMB | USD | 34356354 | ||
KHLT | USD | 200000 | 54764 | LOMB | EUR & ALL OTHER | 46354636 | ||
KHLT | GBP | 200000 | 1241 | KHLT | USD | 54764 | ||
KHLT | GBP | 200000 | 1241 | KHLT | EUR | 486785 | ||
LOMB | EUR | 200000 | 46354636 | KHLT | ALL OTHER | 1241 | ||
LOMB | EUR | 200000 | 46354636 | JD | USD | 3434534 | ||
LOMB | EUR | 200000 | 46354636 | JD | GBP | 45634 | ||
LOMB | EUR | 200000 | 46354636 | JD | CNH | 76572 | ||
LOMB | USD | 200000 | 34356354 | JD | MAD | 23677 | ||
LOMB | GBP | 200000 | 56765 | JD | EUR & ALL OTHER | 3532 | ||
LOMB | EUR | 200000 | 46354636 |
Solved! Go to Solution.
Hi @HB2104,
Try a calculated column like this:
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
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.
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:
CoCd | Currency | Net Due Date | Day of Week | Calc 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 -
This solution requires more testing but seems to work:
Best Regards,
Alexander
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.
Hi @HB2104,
Try a calculated column like this:
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
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:
CoCd | Currency | Net Due Date | Day of Week | Calc Date |
KHLT | USD | 9/19/23 | Tuesday | ? |
LOMB | EUR | 9/15/23 | Friday | ? |
Calendar Rules
Company Code | SAP Payment Run Day | SAP Pulls # of Business Days | Bank Clears # of Business Days |
KHLT | Monday | 7 | 2 |
KHLT | Wednesday | 7 | 2 |
KHLT | Friday | 7 | 2 |
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
30 | |
15 | |
11 | |
10 | |
9 |