topic Re: DAX If Vlookup Statements in DAX Commands and Tips

DAX If Vlookup Statements

HB2104 — Mon, 25 Sep 2023 17:20:06 GMT

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

Re: DAX If Vlookup Statements

barritown — Mon, 25 Sep 2023 18:51:54 GMT

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

My YouTube vlog in English

My YouTube vlog in Russian

Re: DAX If Vlookup Statements

HB2104 — Mon, 25 Sep 2023 20:31:32 GMT

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

Re: DAX If Vlookup Statements

barritown — Mon, 25 Sep 2023 20:40:49 GMT

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

Re: DAX If Vlookup Statements

HB2104 — Mon, 25 Sep 2023 22:22:45 GMT

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.

Re: DAX If Vlookup Statements

barritown — Tue, 26 Sep 2023 07:07:27 GMT

There shouldn't be any problems with text.

Re: DAX If Vlookup Statements

HB2104 — Tue, 26 Sep 2023 17:18:35 GMT

Works great, thank you!

Re: DAX If Vlookup Statements

HB2104 — Tue, 26 Sep 2023 21:00:13 GMT

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.

Re: DAX If Vlookup Statements

barritown — Wed, 27 Sep 2023 12:55:35 GMT

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?

Re: DAX If Vlookup Statements

HB2104 — Wed, 27 Sep 2023 14:35:42 GMT

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

Re: DAX If Vlookup Statements

barritown — Wed, 27 Sep 2023 20:37:13 GMT

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

Re: DAX If Vlookup Statements

HB2104 — Thu, 28 Sep 2023 01:47:18 GMT

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.

Re: DAX If Vlookup Statements

barritown — Thu, 28 Sep 2023 08:25:54 GMT

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?

Re: DAX If Vlookup Statements

HB2104 — Thu, 28 Sep 2023 15:38:47 GMT

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!!

Re: DAX If Vlookup Statements

barritown — Mon, 02 Oct 2023 09:22:19 GMT

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

https://www.dropbox.com/scl/fi/n4rgr2xtqb7t61dm1g58n/community-pdate.pbix?rlkey=ct90a1xacdlrpiy9cr2t60wrv&dl=0

This solution requires more testing but seems to work:

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian