topic Re: Error with Lookuptable "A table of multiple values are supplied" in DAX Commands and Tips

Error with Lookuptable "A table of multiple values are supplied"

Krisvenkata11 — Sat, 16 Apr 2022 17:35:47 GMT

I am trying to prorate the data based on the current month data(cut off date is April 14). I need to take cutoff value which is on April14th and prorate it to all days in the month of May

I am getting a error "A table of multiple values are supplied" while executing below

LatestColumn = LASTDATE(SUMMARIZE(FILTER('Sheet1 (2)', 'Sheet1 (2)'[Work Item] <> 0),'Sheet1 (2)'[Date]))

----------------------------------------

Findval=

var k = FIRSTNONBLANK('Sheet1 (2)'[LatestColumn], 1)

Var X= LOOKUPVALUE('Table 1 (Sheet1)'[Sales], 'Table 1 (Sheet1)'[Date], k)

my current input is:

Date	Sales
4/1/2022	10
4/2/2022	5
4/3/2022	4
4/4/2022	6
4/5/2022	3
4/6/2022	6
4/7/2022	5
4/8/2022	22
4/8/2022	22
4/9/2022	20
4/10/2022	22
4/11/2022	24
4/12/2022	23
4/13/2022	25
4/14/2022	20
4/14/2022	0
4/15/2022	0
4/16/2022	0
4/17/2022	0
4/18/2022	0
4/19/2022	0
4/20/2022	0
4/21/2022	0
4/22/2022	0
4/23/2022	0
4/24/2022	0
4/25/2022	0
4/26/2022	0
4/27/2022	0
4/28/2022	0
4/29/2022	0
4/30/2022	0
5/1/2022	0
5/2/2022	0
5/3/2022	0
5/4/2022	0
5/5/2022	0
5/6/2022	0
5/7/2022	0
5/8/2022	0
5/9/2022	0

and expected output ise

Date	Sales
4/1/2022	10
4/2/2022	5
4/3/2022	4
4/4/2022	6
4/5/2022	3
4/6/2022	6
4/7/2022	5
4/8/2022	22
4/8/2022	22
4/9/2022	20
4/10/2022	22
4/11/2022	24
4/12/2022	23
4/13/2022	25
4/14/2022	20
4/14/2022	0
4/15/2022	0
4/16/2022	0
4/17/2022	0
4/18/2022	0
4/19/2022	0
4/20/2022	0
4/21/2022	0
4/22/2022	0
4/23/2022	0
4/24/2022	0
4/25/2022	0
4/26/2022	0
4/27/2022	0
4/28/2022	0
4/29/2022	0
4/30/2022	0
5/1/2022	20
5/2/2022	20
5/3/2022	20
5/4/2022	20
5/5/2022	20
5/6/2022	20
5/7/2022	20
5/8/2022	20

Re: Error with Lookuptable "A table of multiple values are supplied"

tamerj1 — Sat, 16 Apr 2022 17:32:13 GMT

Hi Krisvenkata11

Do you actually have the date Apr. 14th twice? Are you trying to create a neasure or a calculated column?

Re: Error with Lookuptable "A table of multiple values are supplied"

Krisvenkata11 — Sat, 16 Apr 2022 17:41:43 GMT

Yes, i have the Apr 14 data twice. I am creating a column.

Apr 14 - 20

Aprl 14 - 0

Single value works fine. My challenge how to handle above?

Re: Error with Lookuptable "A table of multiple values are supplied"

tamerj1 — Sat, 16 Apr 2022 17:59:07 GMT

Krisvenkata11

You can achieve than in many ways. One way to start with retrieving the last date with sales

VAR TableWithSales =
FILTER ( Table, Table[Sales] > 0 )

VAR LastDateWithSales =

MAXX ( TableWithSales, Table[Date] )

then retrive the sales value

VAR LastDateSales =
MAXX ( FILTER ( TableWithSales, Table[Date] = LastDateWithSales ), Table[Sales] )

then RETURN

IF ( Table[Sales] = 0, LastDateSales, Table[Sales] )

Re: Error with Lookuptable "A table of multiple values are supplied"

Krisvenkata11 — Sat, 16 Apr 2022 18:24:37 GMT

Thanks a ton, this will work if i have everything in one table but my issue is:

I have a table called 'DatesAll' which contains all the dates from Jan1 st to Dec 31st 2022

I am joining it with sales table which is having the DateSale Column till April 14th(cut off Date)

When i am using Maxx function it is not allowing me to refer the Date column in DatesAll table and only showing the Measures of DatesAll table not columns. Is cross table reference not allowed in Maxx function although both are joined?

Re: Error with Lookuptable "A table of multiple values are supplied"

tamerj1 — Sat, 16 Apr 2022 18:47:09 GMT

Krisvenkata11

Can you please share some screenshots?

Re: Error with Lookuptable "A table of multiple values are supplied"

Krisvenkata11 — Sat, 16 Apr 2022 19:07:41 GMT

Sales Table Data

Date Table( from Jan 1st to 31st Dec)

Below is not taking column from DateTable and expecting only SalesTables Columns

VAR LastDateWithSales = MAXX ( TableWithSales, 'SalesTable'[Date] )

Re: Error with Lookuptable "A table of multiple values are supplied"

tamerj1 — Mon, 18 Apr 2022 07:15:45 GMT

Krisvenkata11
Here is a sample file with the solution

You cannot add rows to an existing table in power bi but you can add columns. What you call date table can actually be your new sales table. This is one option. Other option is to rely on a table visual and utilize measures.

Option 1

https://www.dropbox.com/t/IwoZ27YoMQBMbuyQ

Here I suppose you have only the Sales table. Then we can start creating a new sales table that contains all the dates up to the end of the year.

All Dates Sales = CALENDAR ( MIN ( Sales[Date] ), DATE ( YEAR ( MAX ( Sales[Date] ) ), 12, 31 ) )

Then create one to many relationship with the original sales table

Now you can add a new column in the new table to retrieve sales for respective dates and last date sales for future dates. Here I prefer to use SUMX in order to aggregated all the sales of a day in case you have multiple sales in on day

Sales = VAR CurrentDateSales = SUMX ( RELATEDTABLE ( Sales ), Sales[Sales] ) VAR TableWithSales = FILTER ( Sales, Sales[Sales] > 0 ) VAR LastDateWithSales = MAXX ( TableWithSales, Sales[Date] ) VAR LastDateSales = SUMX ( FILTER ( TableWithSales, Sales[Date] = LastDateWithSales ), Sales[Sales] ) RETURN IF ( 'All Dates Sales'[Date] > LastDateWithSales, LastDateSales, CurrentDateSales )

Option 2

https://www.dropbox.com/t/j5GqpHeLUJqydJpV

Create The Date table using the same code as above. Then creating one to many relationship between the two tables. Then write a Measure

Sales Amount = VAR CurretDate = MAX ( 'Date'[Date] ) VAR LastDateWithSales = CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS ( 'Date' ) ) VAR CurrentSales = SUM ( Sales[Sales] ) VAR LastDateSales = CALCULATE ( SUM ( Sales[Sales] ), Sales[Date] = LastDateWithSales, REMOVEFILTERS ( 'Date' ) ) RETURN SUMX ( VALUES ( 'Date'[Date] ), CALCULATE ( IF ( CurretDate > LastDateWithSales, LastDateSales, CurrentSales ) ) )

In a table visual place the Date[Date] (Do not summarize) and then place the measure

Please let me know if you have any doubt or any question.

Re: Error with Lookuptable "A table of multiple values are supplied"

tamerj1 — Tue, 19 Apr 2022 09:24:46 GMT

Hi Krisvenkata11

here is the final solution based on option 2

Re: Error with Lookuptable "A table of multiple values are supplied"

tamerj1 — Thu, 21 Apr 2022 05:25:39 GMT

Krisvenkata11
Following to our messages, please refer to sample file with the updated solution https://www.dropbox.com/t/ChEaKYhinqk5wlVK

The number of working days shall be within a separate dimension table as shown in the pictures. I believe this is an easy job however please let me know if you need any help on that.
Then adjust your model as shown in the screenshot and update the code as follows

Sales Amount = VAR CurretDate = MAX ( 'Date'[Date] ) VAR CurrentWeekDay = WEEKDAY ( CurretDate ) VAR WorkingDays = SELECTEDVALUE ( 'Working Days'[Cus_Bus_Days] ) VAR LastDateWithSales = CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS ( 'Date' ) ) VAR CurrentSales = SUM ( Sales[Sales] ) VAR LastDateSales = CALCULATE ( SUM ( Sales[Sales] ), Sales[Date] = LastDateWithSales, REMOVEFILTERS ( 'Date' ) ) VAR Result = SUMX ( VALUES ( 'Date'[Date] ), CALCULATE ( IF ( CurretDate > LastDateWithSales, IF ( CurretDate <= EOMONTH ( LastDateWithSales, 0 ) || DIVIDE ( CurrentWeekDay * 6, WorkingDays ) = 1, 0, LastDateSales ), CurrentSales ) ) ) RETURN Result

I hope this answers your query

Re: Error with Lookuptable "A table of multiple values are supplied"

tamerj1 — Thu, 21 Apr 2022 05:27:15 GMT

Krisvenkata11
Following to our messages, please refer to sample file with the updated solution https://www.dropbox.com/t/ChEaKYhinqk5wlVK

I hope this answers your query