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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
dax
Community Support
Community Support

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

Anonymous
Not applicable

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

dax
Community Support
Community Support

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

Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.