Forum Discussion

ankita_7's avatar
ankita_7
Helper II
6 years ago
Solved

Create multiple rows for dates between start and end dates in the same table for each resident no

I am joining multiple tables and getting the result where there are cono,resno, date (startdate) ,enddate and admit_disch columns. I have created a table that stores the result of this joins. I have ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi ankita_7 ,

     

    In Power Bi you can use CROSSJOIN() function.

    First of all you will need to create a calendar table.

    CALENDAR = CALENDARAUTO()

    Here's the example.

    Table 2 = CROSSJOIN('Table',FILTER('CALENDAR','CALENDAR'[Date]>MIN('Table'[start date])&&'CALENDAR'[Date]<MAX('Table'[end date])))

     

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi ankita_7 ,

 

In Power Bi you can use CROSSJOIN() function.

First of all you will need to create a calendar table.

CALENDAR = CALENDARAUTO()

Here's the example.

Table 2 = CROSSJOIN('Table',FILTER('CALENDAR','CALENDAR'[Date]>MIN('Table'[start date])&&'CALENDAR'[Date]<MAX('Table'[end date])))

 

 

Best Regards,

Jay

 

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ankita_7's avatar
ankita_7
Helper II
6 years ago

Hi Anonymous ,

 

I tried this dax and it is giving me an error. I think it is coz there is a column named Date already there in the aht_census_all_table. Please check the screenshot attached below:

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi ankita_7 ,

     

    This error is caused by columns with the same name. Rename the Calendar[date] column will fix the error.

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.