Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

Need Help

4 Replies

  • dax's avatar
    dax
    Community Support
    7 years ago

    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

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        dax
        Community Support
        7 years ago

        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