Displaying a bar with zero values in a bar chart visual

[PaulF0901]

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. 

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.

[PaulF0901]

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

[PaulF0901]

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. 

[kpost]

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]

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])

 

 

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.

 

[PaulF0901]

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:

But strangely no values

[kpost · Accepted solution]

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.