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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.