slicer stops working after I add a measure to my report

[arunbyc]

One of my measures does not affect the way the date slicer works on my Table visual while another measure affects it. Will someone please explain why:

I have  dateTable linked to "work_date" column of "myTable" in the data model. (1 to many)

myTable has the following fields:

     work_date

     Employee_No

     Total_hours 

     Excludable_hours

I have two measures:

Available_Hours = sum(myTable[total_hours]) - sum(myTable[Excludable_hours])
missing_Hours = 8 - sum(myTable[total_hours])

Measure "Available_Hours" does not cause any issues:
I inserted employee_No, work_date, Total_hours and Available_hours fields in the report and everything worked fine. only the dates selected in the slicer were displayed and the Available Hours were displayed for those dates

Measure missing_hours causes issues iwth the dates displayed (ignores slicer):

The moment I added missing_hours measure to the report, all the dates from the myTable appeared on the report, ignoring the date range selected in the slicer.

Employee No, Available hours etc., however,  are displayed only for the dates in the range selected by the slicer.  Blank rows were displayed on all other dates.

While I was able to change the measure  by explicitly filtering the dates to those selected in the slicer by putting the code in calculate with a filter, I do not understand why the Available_Hours measure has not affected the way the dates are displayed in the table visual.

Can someone please explain why some measure affect the way slicer can filter and others do not.

By the way this following code was the one that worked:

Missing Hours =

VAR SelectedDates = VALUES('DateTable'[Date])

RETURN

CALCULATE(

    8 - SUM(MyTable[total_hours]),  MyTable[work_date]>=min(DateTable[Date]) , MyTable[work_date]<= MAX(DateTable[Date])

)

Thanks

AR

[v-mdharahman]

Hi @arunbyc,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are facing some issues with your slicer not working properly with one of the measures. As @burakkaragoz and @rajendraongole1 both responded to your query, kindly go through their responses and check if it solves your issue.

I would also take a moment to thank @burakkaragoz and @rajendraongole1, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[burakkaragoz]

Hi @arunbyc ,

Great question—and you're not alone! This kind of issue usually comes down to filter context and how your measure interacts with the model.

Here’s what’s happening:

• Your Available_Hours measure uses columns directly from myTable, so it respects the slicer naturally.
• But Missing_Hours = 8 - SUM(myTable[total_hours]) doesn’t reference the DateTable at all. So when you add it to the visual, Power BI tries to show it for all dates in myTable, not just the ones selected in the slicer.

That’s why you see extra rows—even though other fields still respect the slicer.

Your fix using CALCULATE with explicit date filtering is spot on:

Missing_Hours = 
VAR SelectedDates = VALUES('DateTable'[Date])
RETURN
    CALCULATE(
        8 - SUM(MyTable[total_hours]),
        MyTable[work_date] >= MIN('DateTable'[Date]),
        MyTable[work_date] <= MAX('DateTable'[Date])
    )

This forces the measure to respect the slicer by applying the same filter logic manually.

So yeah—nothing wrong with your model. It’s just how DAX behaves when a measure doesn’t naturally inherit the slicer’s filter context.

Hope that clears it up!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[arunbyc]

would you recommend the following set up?
Slicer is also from myTable[Workdate]. And the code is simply : if (not isempty(myTable), 8-sum(myTable[totalHours])
Is this not recommendable?

[rajendraongole1 · Accepted solution]

Hi @arunbyc  - 

can you try a simpler version as like below:

Missing Hours =
CALCULATE(
8 - SUM(MyTable[total_hours])
)

As long as your slicer is filtering 'DateTable'[Date], and there's a single active relationship from 'DateTable'[Date] → myTable[work_date], this should behave well — but only if your table visual includes DateTable[Date] and not myTable[work_date].

Hope this works

[arunbyc]

would you recommend the following set up?
Slicer is also from myTable[Workdate]. And the code is simply : if (not isempty(myTable), 8-sum(myTable[totalHours])
Is this not recommendable?