Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
TechR21
Helper V
Helper V

Entry in visual for each day, based on date table

Hi,

 

I have the following tables and columns;

TechR21_1-1674721803152.png

 

 

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:

 

TechR21_4-1674722000508.png

 

 

Any idea??

 

2 REPLIES 2
MAwwad
Super User
Super User

  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.

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?

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.