Forum Discussion
Column Chart showing Legend with no Data
Hi There
I hope someone can help me.
I have a column chart that is showing employee working hour data.
Unfortuantelly, it is also showing in the legend month that is outside my slicer filter. It is also showing data in the future that is not even in the table.
The Table is a Crossjoin:
EmployeeIncidentTable =
CROSSJOIN ( DateTable, J_employees )
Date Table:
DateTable =
VAR StartDate = TODAY() - 730
VAR EndDate = TODAY()
RETURN
ADDCOLUMNS (
CALENDAR ( StartDate, EndDate ),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"MonthName", FORMAT([Date], "MMMM"),
"Weekday", WEEKDAY([Date], 2),
"IsWorkday", IF(WEEKDAY([Date], 2) <= 5, 1, 0)
)
The only thing that seems to work is when setting the slicer to year:
But changing that to Month doesnt work:
Any Idea how to fix taht?
3 Replies
My recommendation is to create a regular relationship between the tables instead of using the
CROSSJOINfunction.The
CROSSJOINessentially generates all possible combinations of dates and employees, which could be why you're seeing months outside your filter range and even future data that doesn’t exist in the table.By creating a proper relationship between the
DateTableand the employee data (using, for example,EmployeeIDand theDate), Power BI will automatically filter the data correctly according to your slicer, whether you're filtering by year or month.
For example, I have a relationship between orders and dates :And I want to see sales by countries:
In some months there are no sales in one of the countries...
In these periods I will see only the existing countries sales :The pbix with the example is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
The Problem is that I want the combination between employee und date. so each employee shoud have for each day one row. However I fugured out that I only have this issue with measures but not with calculated columns. I will try to use calculted columns instead.
Hi, jonasdedual_SP
Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!
Best Regards
Yongkang Hua
