Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rkapukaya
Helper I
Helper I

Direct Query - Join Date Ranges

Hi,

I work with SCD structured tables in dwh model.  I want to create relation between two tables based on two column.

One of  joins is date range join. 

 

I've searched merging tables yet i have to design report in direct query mode. I couldn't manage to create these relation type.

 

pls note: i don't want to create calc. measure to achive date range relation.

 

Here is my data and my sql script;

rkapukaya_0-1614014459135.png

 

 

SELECT
XX.StoreNo,
XX.StoreName,
XX.StoreGroup,
S.SalesDate,
S.StoreSales
FROM Store S
LEFT JOIN StoreSales XX
ON S.StoreNo = XX.StoreNo
AND S.SalesDate BETWEEN XX.BeginDate AND XX.EndDate

2 REPLIES 2
amitchandak
Super User
Super User

@amitchandak , 

 

i dont have surrogate key on my table. Just begindate and endate columns. 

 

at video, table has srg key so it is easily to join with tables.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors