Power BI: DAX or M and how to JOIN

[Enila123]

Hello everyone,

I've got a problem to join two tables in a certain way. These are the data that I get via live connection to SSAS:

Table 1:                                                     Table 2:

   Date                                                           ID                       myValue              StartDate            EndDate

01/01/17                                                        1                            40                   01/01/17            15/02/17

01/02/17                                                        1                            45                   16/02/17            31/03/17

01/03/17                                                        1                            50                   01/04/17               NULL   (which means until today)

01/04/17                                                        2                            80                   15/12/16            02/06/17

     [...]                                                            [...]                          [...]                        [...]                      [...]

For my reporting I need the following table:

   ID                       myValue              StartDate            EndDate           myDate

    1                            40                   01/01/17            15/02/17          01/01/17

    1                            40                   01/01/17            15/02/17          01/02/17

    1                            45                   16/02/17            31/03/17          01/03/17

    1                            50                   01/04/17               NULL             01/04/17

    2                            80                   15/12/16            02/06/17          01/01/17

    2                            80                   15/12/16            02/06/17          01/02/17

    2                            80                   15/12/16            02/06/17          01/03/17

    2                            80                   15/12/16            02/06/17          01/04/17

   [...]                          [...]                        [...]                      [...]                    [...]

So I need to join them considering the date. For every date that is in the Table1 I have to check if it's between StartDate and EndDate and create a new row with the new column myDate (which is filled by Date from Table1).

Does anyone know a possiblity to solve that problem? I'm not even sure if I have to work with DAX or M. There aren't many JOIN functions and I'm not supposed to prepare the data already by using SQL before.

Thanks a lot!

[v-jiascu-msft]

Hi @Enila123,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[v-jiascu-msft]

Hi @Enila123,

You can't do with DAX or M due to the live connection to SSAS. You can try it yourself. Maybe you can do it in the SSAS model.

Best Regards!

Dale