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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
PaulF0901
Regular Visitor

Displaying a bar with zero values in a bar chart visual

Hi,

 

I have a challenge when it comes to displaying a bar on a bar chart for a value that doesn't exist. Let me explain. 

 

I have a table of logged user activities similar to the following:

 

ActivityID, ActivityTime, userEmail, Event,Event Type

1234,10/04/2024,john@ms.com,Created,Report  

2314,8/3/2024,pete@ms.com,Created,Report

 

I need to show a bar chart with each bar being the week number in the year. To do so I have created a calculated field for WeekNumber. This is all fine. Now I have something similar to the following:

 

ActivityID, ActivityTime, userEmail, Event,Event Type,WeekNumber

1234,10/04/2024,john@ms.com,Created,Report,19 

2314,8/3/2024,pete@ms.com,Created,Report,18

 

The problem I have is that, for some weeks, there is no user activity. That particular week number simply doesn't appear on the bar chart visual. It makes sense given that there is no activity for that week but I really need it there. You can see in the screenshot below that there were reports created in week 20 but no workspaces. I really need the workspaces chart to show week 20, just with nothing in it. 

 

PaulF0901_0-1716315815746.png

 

 

The idea of selecting 'show items with no data' doesn't work, presumably because the week number (the X axis) simply doesn't exist for week 20 in this case. 

 

Any idea how I might get the week numbers that don't have activities to actually display in the bar chart?

 

Many thanks for your help in advance.

1 ACCEPTED SOLUTION

date.PNG

Maybe try this?

 

-Make a copy of the "Date/Time" column in your fact table, then convert the data type of that column to Date, then create the relationship with that column.   That will preserve the date/time column, untouched.

 

-Another option (I think I read somewhere that this enables your semantic model to be more optimized) is to split your date/Time column into two columns using Power Query: a Date column and a Time column.   Then have the relationship be on the date column.

 

-You could also just convert your date/time column into a column with data type Date, but you'll lose the Time part.  So it depends on your situation.  If you don't use, and don't plan on using the Time part of your Date/Time column, you can do this with no problems.

View solution in original post

6 REPLIES 6
PaulF0901
Regular Visitor

A million thank you's for your help. You are a true expert.

PaulF0901
Regular Visitor

Frustratingly still nothing. 

 

Both tables exist. Both have a field that is of type Date. 

 

I have added the weekNum from the Date table as the x axis. 

 

I now don't even see the Week numbers in the table under the chart. There is something very weird going on but I'm at a loss as to what it is. 

 

PaulF0901_0-1716325792102.pngPaulF0901_1-1716325819754.png

 

Could you upload a version of this file with only a few rows of data (hopefully more, but i am just assuming it is confidential) but leave everything else the same, so I can fix it and upload the .pbix file as a solution?

kpost
Super User
Super User

Create a Date Dimension Table with a "weeknum" column, then create an active many-to-one relationship between the [Date] Column in the date dimension table and your fact table.

 

Date_Table = CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)) ---this is just an example. Use whatever dates you want, or google CALENDARAUTO() function if you think that would be better.
WEEKNUM = WEEKNUM([DATE])
 

 

model.PNG

 

Then use the weeknum column from your date dimension table as the X axis.

 

See attached .pbix file with the solution.

 

Also, side note, having a date dimension table is best practice anyway (you probably can understand why, now), and you should get in the habit. 👍  It guarantees no "blanks" in your visuals even if there is no data for a given date range, you can use a single date slicer using the Date_Table[Date] field to filter the entire report page even if it contains data from many many fact tables, etc.  And even more reasons than that.  But you get the idea.

soln.PNG

Hi there, 

 

So kind of you to take the time to respond with such detail. 

 

I tried to replicate what you had but, for some strange reason, I'm not getting any values on the Y axis, so no bars at all. 

 

I can only assume that it has something to do with the relationship. My original activities table has multiple dates which could be the same and the format is '25/04/2024 04:30:57'. I don't know if that makes a difference?

 

Everything else certainly looks similar:

 

PaulF0901_1-1716320125418.png

 

But strangely no values

 

PaulF0901_2-1716320152089.png

 

date.PNG

Maybe try this?

 

-Make a copy of the "Date/Time" column in your fact table, then convert the data type of that column to Date, then create the relationship with that column.   That will preserve the date/time column, untouched.

 

-Another option (I think I read somewhere that this enables your semantic model to be more optimized) is to split your date/Time column into two columns using Power Query: a Date column and a Time column.   Then have the relationship be on the date column.

 

-You could also just convert your date/time column into a column with data type Date, but you'll lose the Time part.  So it depends on your situation.  If you don't use, and don't plan on using the Time part of your Date/Time column, you can do this with no problems.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.