Column Chart showing Legend with no Data

[jonasdedual_SP]

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?

[jonasdedual_SP]

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.

[Anonymous]

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

[Ritaf1983]

Hi @jonasdedual_SP 

My recommendation is to create a regular relationship between the tables instead of using the CROSSJOIN function.

The CROSSJOIN essentially 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 DateTable and the employee data (using, for example, EmployeeID and the Date), 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