Date Table won't appear in visual

[GunnerJ]

I have a table that has a column with dates on it. Some dates don't appear as there simply isn't any data for those specific days. I created a simple date table that shows today through two weeks with the hope that using it will allow my visual to show the empty days as empty. I have a 1:M relationship between my date table and the column with dates in it for my data. When I 

Date Table:

Date Table 2 = CALENDAR(TODAY(), TODAY()+13)

Current graph. All values summed under "blank" when they should be spread out over a two week span with some days showing no bar.

This is what I want my graph to look like.

Any help in pointing out what I need to change would be greatly appreciated. 

Thanks 

[edhans · Accepted solution]

Your Model should look roughly like this:

And your visual should look roughly like this with these fields from the specific tables I am pointing to:

And the Row Count measure is this:

Row Count = 
VAR RowCount = COUNTROWS(Sales)
RETURN
IF(RowCount = BLANK(), 0, RowCount)

If that isn't working, please at a minimum show us the fields you are using in your visual, as well as the model view, or better yet, just put a PBIX file with no confidential info in OneDrive and share it here.

[kentyler]

Power BI has a setting to show items with no data https://docs.microsoft.com/en-us/power-bi/desktop-show-items-no-data

[edhans]

1. Make sure the date you are putting in your visual is from your Date table, not your data table.
2. You can click on the dropdown next to the Date field in the visual and tell it to "Show items with no data"

Often visuals won't show blanks. Another approach is to use a measure.

Total Sales = 
VAR TotalSales = SUM(Sales[Sales])
RETURN
IF(TotalSales = BLANK(), 0, TotalSales)

That will return 0, not blank.

[GunnerJ]

After I checked "Show blanks" I see the dates but my data simply isn't filling in as a would expect. It doesn't recognize the dates it would seem. 

I double checked and the date field in my data table is a date type. I don't understand why it won't populate correctly. All I want is a count of the rows by the displayed dates.

@edhans 

[edhans · Accepted solution]

Your Model should look roughly like this:

And your visual should look roughly like this with these fields from the specific tables I am pointing to:

And the Row Count measure is this:

Row Count = 
VAR RowCount = COUNTROWS(Sales)
RETURN
IF(RowCount = BLANK(), 0, RowCount)

If that isn't working, please at a minimum show us the fields you are using in your visual, as well as the model view, or better yet, just put a PBIX file with no confidential info in OneDrive and share it here.

[GunnerJ]

@edhans  sorry didn't mean to accept as solution yet.

[GunnerJ]

https://www.dropbox.com/s/h9xae0gwu1fqjrl/OZARKSGO%20-%20SUBSCRIBER%20DASHBOARD.pbix?dl=0

On page 4 of the file the top graph shows the incomplete visual and the bottom graph is what it should more or less look like. 

Thank you 

@edhans 

[edhans · Accepted solution]

Is this what you are looking for? You said page 4, but this is on page 5, and I saw the bar chart. I didn't fully understand what you meant when the top chart should look like the bottom chart when one is a line chart and the other is a bar chart. But I do see where the line chart is missing data.

I made a new measure called "Reconnect Residential" that is next to your "Reconnects Residential" (mine is singular, yours plural) and I get a purple line all the way across, with most being zero as that table only has days, but my line shows the zeros whereas yours stops.

So it is the measure I posted above, but specifically:

Reconnect Residential = 
VAR RowCount = COUNTROWS('INSTALL 2 WEEKS RECONNECTS RESIDENTIAL')
RETURN
IF(RowCount = BLANK(),0,RowCount)

Is that what you were looking for? 

As an aside, I made your date table a true date table. You have to click on it in the Report tab, select "Mark as Date Table" and then point out the date field. Not terribly relevant in this example, but it isn't a date table until you tell Power BI it is.

The updated PBIX is here. If that isn't right, can you be more specific about what you want telling me the exact name of the tab that may have two visuals that are the same, where one is right and the other is wrong?

[GunnerJ]

The Date Table seems to have been my issue. When you say you made it a "true" date table what does that involve? I'm hoping to learn and avoid this kind of mistake in the future.

Thank you for your help!

@edhans 

[edhans]

It wasn't the issue actually, it was the measure. Your "implicit" measure (you dragged a value and Power BI created a measure) didn't handle no records, so no records returns BLANK, which doesn't show up in visuals.

My "explicit" measure said "if the total is blank, then return 0, otherwise, return the real row count", and 0's do show up in visuals.

As for date tables, once you've created them:

1. Right-click on it
2. Select Mark As Date Table
3. Mark as Date table (again, on the sub-menu. Seems redundant to me, but that is the way it is)
4. On the dialog that opens up, select the actual Date field in your table. It will then run a validation to ensure it is a true date table, and tell you why it isn't if it cannot validate. (Missing dates, text fields vs date fields, etc)

Then I turn off ALL automatic date logic in the options. You want to control the date logic. Don't let Power BI do it as you could get wierd results you cannot explain until you realize it is using a date table it created from a PO table with or something. It will actually create a hidden date table for every date field it finds in the model if you let it do it automatically. I always just one one date table.

[edhans]

@GunnerJ - I made a few changes to your model to make this easier. You had a model that looked like this for this visual:

That has one date table but four FACT tables that are essentially the same data. I copied those tables into a new PBIX file and created a star schema model that has 2 DIMENSION tables (Date and Install Types), and just one FACT table.

With this, you only need one measure to make this work instead of four measures. Plus, if you want to change/tweak the measure, you only do it one time. There is no need to worry about the tables filtering each other as well, as they are now in one normalized table.

I did this in Power Query. That is why I copied the data to a new PBIX file as I didn't have access to your database sources. In Power Query this is what it looks like - you can see this in the View, Query Dependencies button in Power Query:

Here is what I did:

1. Unload your 4 original tables. Right-click on them and uncheck the "Enable Load" option. The query names will become italicized indicateing they are no longer loaded to the data model. This will trash your visual, but you'll see in a minute this will rebuild much faster.
   1. In each table, I formatted them the same (data types, same column names) and added a custom column for each called [Install Type] with a text value of the type table it is - Connect Commercial, Connect Residiental, etc.
2. I then selected the first one, clicked on the dropdown in the Home ribbon for Append Queries, then Append Queries as New. I slected the "Multiple tables" box and made sure all 4 were shown. I named this query "Combined Install Tables" and made sure it is also not loaded.
3. I then right-clicked on that new query that has all 4 install types and created a reference to it and called it varStartDate. Then I selected the date column, Removed all Other columns, then on the Transform ribbon, selected the Date dropdown, and "Earliest." It is now a scalar value of 1/30/2020, the first date in your model. Make sure it is also not loaded.
4. I then duplicated varStartDate and called it varEndDate. I then changed the "Calculated Earliest" step to "Calculated Latest" and changed the function it used from = List.Max(Source[SCHEDULED_DATE]) to = List.Min(Source[SCHEDULED_DATE]).
5. I then pasted in a prebuilt section of M Code into a new blank query to create the date table.
   1. Ceate a new Blank Query from the New Source menu.
   2. On the Home ribbon, select Advanced Editor
   3. Paste in all of that M code I have into a new query.
   4. You'll notice the first line is this: = {Number.From(varStartDate)..Number.From(varEndDate)}.
   5. That will generate a list of consecutive dates from the first date in your model to the last date in your model. Notice the varStartDate and varEndDate variables from #3 & #4 above being used.
   6. I named this query Dates and it does have Enable Load enabled.
6. I created another reference from the "Combined Install Tables" query and called it "Installs Scheduled." This is also has Enable Load enabled. Note: I could have loaded "Combined Install Tables" but I never load a query in Power Query that has other queries dependent on it. As soon as you make a tweak to it for your model, it can wreck dependent queries. This isolates it from others. Just a habit I've gotten into.
7. I then created one final reference from "Combined Install Tables" and called it "Install Types"
   
   1. Select the column you created called "Install Type" and Remove Other columns
   2. Right-click on that column and select Remove Duplicates
   3. Set this query to load.

Now queries in #'s 5, 6, & 7 are loaded in the model. They have red boxes around them in the above view and you can see they say "Loaded."

• Create the relationships as shown above.
• On the Report tab, right-click on the Date table, Mark as Date Table, and select the Date column.
• Create your visual.
  • Drag the Date from the Date table to the Axis
  • Drag the "Install Types" field from the Install Types table to the Legend.
  • Drag the Install Count measure (shown below) to the values.

Install Count measure:

Install Count = 
VAR RowCountOfInstalls = COUNTROWS('Installs Secheduled')
RETURN
IF(
    RowCountOfInstalls = BLANK(),
    0,
    RowCountOfInstalls
)

I think you'll find this a much more flexible approach. You'll have to spend more time in Power Query building the right tables for the model, but it will save you a ton of work and make the DAX much easier in the report tab. Note too that as you refresh, the date table will automatically expand/contract based on the dates from those source tables.

Since your Date table might be feeding into other visuals with different data, you can have it use other queries to get the values for varStarDate and varEndDate. I've even had models that picked the earliest/latest dates from a number of tables to create a Date table I never have to touch again. Just requires a bit more work.

My revised PBIX file is here for you to look at if you want.

[amitchandak]

If you can try. Put the count of date from date table into tooltip and check