topic Re: Lookupvalue over 2 Columns in DAX Commands and Tips

Lookupvalue over 2 Columns

awolf88 — Sun, 28 Nov 2021 11:30:43 GMT

Hi girls & guys,

I have 2 very simple tables:

1.) Sales

2.) Product Table

Now as you can see within the Product table, there's one item, "2536" or "TV Remoate", that comes at different pricing options, depending on the quantity customers purchase it at.

Since there are no unique values, I'm staying away from building a relationship within the model itself. I would much rather create a separate, calculated column within the Sales table, listing the price according to quantity ordered.

Now I would presume, that the easiest way would be a Lookupvalue function creating the column. But I'm having trouble entering it so it aggregates properly.

Could anyone kindly help me out or suggest if there's an easier option to do this?

Thanks in advance!

Re: Lookupvalue over 2 Columns

Jihwan_Kim — Sun, 28 Nov 2021 17:26:02 GMT

Hi,

I suggest creating Product table like below.

And then, Range of MIn column and Max column can be used like below.

Please check the attached pbix file. All Calculated Columns are in the attached pbix file.

Re: Lookupvalue over 2 Columns

awolf88 — Mon, 29 Nov 2021 17:43:33 GMT

Did the trick, beautifully! Thank you kindly!!

Re: Lookupvalue over 2 Columns

awolf88 — Mon, 04 Apr 2022 13:40:19 GMT

Hi there!

may I pick up this problem from before and inquire about one more piece of coding to what has worked as a charm so far?

My Product table now has 2 additional columns with a mix of current and historic prices that have changed due to price increases. I would now need the code to not only aggregate by quantity amount but also by which time window the sales price has been valid for if that makes sense?

Thanks guys!