Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIs 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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingPower BI has a setting to show items with no data https://docs.microsoft.com/en-us/power-bi/desktop-show-items-no-data
Help when you know. Ask when you don't!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAfter 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.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportinghttps://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
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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe 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!
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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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:
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."
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIf you can try. Put the count of date from date table into tooltip and check
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |