Forum Discussion
Helper IDAX 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!
| 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 |
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.
There shouldn't be any problems with text.
- Works great, thank you!
14 Replies
- barritown
Solution Sage
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
HB2104I 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.
- HB2104
Thank you, awesome! I assume Tb12 in this is the table for the bank numbers?
- barritown
Yup, it's the second table with the columns [Company], [Currency] and [Bank Info]. In fact, it's "Tbl2", short for "Table 2". 🙂
- HB2104
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.
- barritown
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/23Should [SAP Pulls # of Business Days], [Bank Clears # of Business Days] be used in calculations?
- HB2104
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!!
- barritown
It's possible. For example, please check the file available here -
This solution requires more testing but seems to work:
Best Regards,
Alexander