topic Re: Lookup Value Horizontally in DAX Commands and Tips

Lookup Value Horizontally

John1232 — Wed, 22 Mar 2023 20:53:28 GMT

How do you Hlookup on Dax or PBI, Say as example I have this table and I have values in horizontal mode, I need to search up the date and matching the date with the header date I need that to bring me a value. In excel is very simple I just make an HLOOKUP to crosscheck for the date on top of each column and bring me back the value depending on the date and row selected.

how do I replicate this exact same scenario on PBI, I need to crosscheck a date with the header and then bring me the value. I dont want to unpivot because I have unique values that I cant lose . Ive tried Lookup but it will not let me select more than one column and I need to search in all horizontally, any help will be appreciated

Re: Lookup Value Horizontally

amitchandak — Thu, 23 Mar 2023 02:02:04 GMT

John1232 , I think you try to use unpivot in power query

Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g

Re: Lookup Value Horizontally

John1232 — Thu, 23 Mar 2023 12:14:17 GMT

Thank you very much for your answer

The problem is that I need to use the unique value to match this with another table, and when I unpivot this will create duplicates in the unique value and will no longer support a relationship cause it will count as many to many from both tables giving an error.

Re: Lookup Value Horizontally

WinterMist — Thu, 23 Mar 2023 15:06:25 GMT

John1232

I am confused by the expected results from your screenshot.

Row 1 of Expected Results shows Answer = Value 3

- WHERE Unique Value = 1 AND Date = 3/3/2023

- However, this does not match the source Lookup table. Answer should be Value 1.

Row 2 of Expected Results shows Answer = Value 3

- But again, based on the source Lookup table, the answer should be Value 1.

Row 3 shows Answer = Value 2, but should be Value 3.

Only Row 4 appears to be correct with Value 2.

Can you help me understand how you are calculating these values that seem to be incorrect?

NOTE 1: You mention that when you unpivot, it creates "duplicates" in the unique value column and will no longer support a relationship. However, these are not true duplicates. They actually reflect the data that is already there prior to unpivoting. If you leave the data as is, you still cannot use Unique Value by itself to get your Answer. It must be a combination of 2 items: Unique Value + Date. This will be true regardless of whether you unpivot or not.

NOTE 2: Due to two date columns being identical (3/14 & 3/14), you actually do have 4 instances of true duplicates:

Are these valid duplicates? Or do they need to be corrected?

Can you provide a small sample data set for the other table you need to join to?
Does that table have both the "Key" as well as the Date?

(I say "Key" because this column is not a true Key. The actual Key is Key + Date.)

Regards,

Nathan