cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

SSAS Tabular Model Date Range Join

Hi All,

 

I have a SSAS Tabular model on which my PBI reporting is based.

 

I have not found a way to join the tables using a range. Where the join is a "between"

 

Other reporting solutions are able to do this easily. Is there a DAX formula for this?

 

EG Join between Sales and Date table where Sales is between Min Date and Max Date of the Date table.

 

Thanks!!

Basia

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: SSAS Tabular Model Date Range Join

Hi Basia

According to your description, did you mean you want to generate a table like sql with join syntax: “select a.xx,b.ss  from a left join b on a.id=b.id where  b.date between ..and …”? If so, you could try to create a table in powerbi like below

Capture11.PNG

join2 =
FILTER (
    CROSSJOIN (
        date1,
        SELECTCOLUMNS (
            salet,
            "saleid", salet[id],
            "saleamount", salet[amount],
            "saledate", salet[date]
        )
    ),
    date1[id] = [saleid]
        && date1[startdate] <= [saledate]
        && date1[enddate] >= [saledate]
)

Best Regards,
Zoe Zhi

Advocate I
Advocate I

Re: SSAS Tabular Model Date Range Join

Thanks Zoe,

 

This looks really cool! Where do I apply the filter?

 

Is it applied in each sales measure? or can I globally apply it so that all measures that use the date table can be reported over a date range?

 

Thanks again

Basia

Highlighted
Community Support
Community Support

Re: SSAS Tabular Model Date Range Join

Hi Basia

 

According to your description, it seems that you want to join two tables into a new table, so my expression is to create a new table, you could use the new table to create visual and apply filter on it

 

Best Regards,
Zoe Zhi

Highlighted
Advocate I
Advocate I

Re: SSAS Tabular Model Date Range Join

@dax 

 

Hi Zoe, 

 

Thanks for your explanation.

I guess I'm after a run-time table that would be used for the purpose of graphing all sorts of measures against a time line that is governed by a single drop down Month Filter/Slicer.

 

I have not been able to achieve this yet.

 

With your solution, I would need to create a separate measures table for each measure and I have 100+ measures, so that is just not feasible.

 

I'll keep searching/asking....

 

Thanks for your help.

 

Basia

 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors