Look up with direct query and with imported table as the lookup table

[Bone]

Hello - I have a Direct query and an imported look up table.

The direct query contains many timesheet entries and I am trying to create a column that has the Sunday date for each time entry.

For example, timesheet entry for 5/4/2022 would have a weekstarting date of 5/1/2022.

I have been unable to make this work with the lookup function.  Another post states that lookup doesn't work with Direct Query, is this true?  if so, what is the alternative?

[Bone · Accepted solution]

I was able to fix my lookup table by just making the date the primary key.  Simple solution but for some reason not mentioned anywhere.

[Bone]

I have many spreadsheets I want to convert to PowerBI and I will need to use lookup tables, so it would be really helpful to know how to make this work with a direct query.  So far I get error messages similar to the one above.

[Bone]

@amitchandak thanks, I like this idea. I get the below error.  In general, whatever I try anything, Power BI seems to be telling me that it doesn't like the fact that many rows in my direct query have the same date.  Of course it does because there are many entries every day. 

 How do you interpret the below error message?  

[amitchandak]

@Bone , Try in you Date table. but there is limit , what you can use in a new column

Week Start date = 'Date'[Date] -1*WEEKDAY('Date'[Date],1) +1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],1)

[Bone]

I need a more elaborate formula because the dates exist more than once. 

[v-rzhou-msft]

Hi @Bone ,

LOOKUPVALUE function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

According to your screenshot, I think you are now creating a measure to calculate the weekstarting date. I create a sample and reproduce in my sample.

As the error mentioned, you need to use max/min to catch data in text/date format... and so on.

Try this code to create a measure.

Weekstartingdate (Measure) = 
VAR _DATE = MAX('Date in DQ'[Date])
RETURN
_DATE - WEEKDAY(_DATE,1)+1

Or you can create a calculated column by your current code.

Weekstartingdate (Calculated Column) = 
'Date in DQ'[Date] - WEEKDAY('Date in DQ'[Date],1)+1

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Bone]

Hello Rico - 

I probably haven't explained the situation very well, so let me do that now.

Time Entry Table is a Direct Query

It has many rows with (the pertinent columns listed below). I am trying to create a column that contains the Sunday date for each of the rows in the table (the green column is what I am after).  Power BI is kicking back an error message because dates are repeated in each row.  The formulas only seem to work if the date in the DATE column only happens once.