Forum Discussion

TechR21's avatar
TechR21
Icon for Helper V rankHelper V
3 years ago

Entry in visual for each day, based on date table

Hi,

 

I have the following tables and columns;

 

 

Run table; contains information about runs on a daily base. Its possible that some days there is no run, so there will be no entry in this table. For example 12-jan

Based on the time in the Start Date column, the correct date will be displayed in the Date column in this table, so this is a calculated column

 

Date table: I created this table with dates from 1-1-23 till 31-12-24 with the idea to join this on the run table, and be able to select all dates, also if on a date there was no run.

 

Unfortunately as you can see in the report visual, I dont get the expected result, and the data of the date table isnt showing up. I tried different joins but not getting the correct result, either empty or just the same dates as in the run table.

 

What i would like to get is something like the following where there is an entry on the days there was no run:

 

 

 

Any idea??

 

Need Help

2 Replies

  • MAwwad's avatar
    MAwwad
    Icon for Solution Sage rankSolution Sage
    3 years ago
    1. Open the Power BI report and go to the Home tab.
    2. Select the Edit Queries button to open the Query Editor.
    3. In the Queries pane, right-click on the Run table and select Reference.
    4. Right-click on the Date table and select Join.
    5. In the Join dialog box, select the Left outer join option.
    6. Select the Date column from the Date table as the join column.
    7. Select the Start Date column from the Run table as the join column.
    8. Click OK to create the join.

    This should create the left outer join between the Run table and the Date table, and you should see all the dates from the Date table in the report visual, even if there was no run on that date.

    Make sure that the date column in the Run table is in the correct format, that it is a date, and that the date column in the Date table is in the same format, it could be possible that the join is not made properly because of the format issue.

    • TechR21's avatar
      TechR21
      Icon for Helper V rankHelper V
      3 years ago

      I think your steps were not fully correct but I managed to do it 🙂

       

      the steps i took:

      1. reference run table

      2. merged 2 tables, the date table in the top, and run table as second table, than left outer join.

       

      Now i have indeed the two tables together, with all the dates of the date table.

       

      Question: since my run table gets updated via odatafeed almost daily (when there has been a run), will this merged table get updated as well?