Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Best way to add a calendar table with data from sql

Hi all. First of all thank you for your help!  Let me explain to you my current situation: Source of the data: Database. Date fields: 2, so I'd like to have two calendar tables. Is there any cal...
Need Help
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Not totally clear on your scenario.  Unless your source calendar table has special columns in it, I would just make two DAX calendar tables that uses MIN and MAX from your fact table inside the CALENDAR function.  FYI on an article/video that provides DAX (and M) calendar table expressions that also include columns that do not require a sort by column.

 

No Sort Date Tables! – Hoosier BI

 

Pat

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks Pat!

     

    I was thinking about using DAX/M but tried to use the calendar table from my database to improve the performance, but thank you! I'll use your DAX script

     

    About my scenario maybe I didn't explain it well enough:

    • I have a sql script that use data from several tables. At the end of the script I get one table (not a physical one) with the data I need
    • As the data I need it's not stored in just one fact table I can't filter out the calendar table using min/max from the fact table in the database. That's why I tried to do it in Power BI.
    • That means I would have two tables in PBI, the one with the information and the calendar table (with data from so many years)

    Tha main thing would be: How could I filter the calendar table based on another table stored in Power BI and not in the database? I could make it but the performance was so bad and the time to refresh increased a lot.

     

    Thanks!