Slicer and Allexcept

[tamiribas]

Hi

The following DAX expression returns the correct value for [DaysPerDept].

DaysPerDept = CALCULATE([Total Days], ALLEXCEPT(data, data[Dept]))

However, when I use the date slicer, the values of [DaysPerDept] remain the same and do not show the correct result just for the filtered rows.

What should I change in the above measure to accommodate such filtering?

Thank you

Tamir

[tamiribas]

Hi @v-cgao-msft 

Thank you for the suggestion.

When a single date is selected, the result is correct. 

However, when multiple dates are selected, the result is incorrect and shows the daily value.

Thank you,

Tamir

[v-cgao-msft]

Hi @tamiribas ,

Could you please confirm whether it is possible to upload a file without private data for further testing?
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[tamiribas]

@v-cgao-msft 

The current demo pbix is the same as what I have.

Thank you

Tamir

[v-cgao-msft]

Hi @tamiribas ,

Please try this measure:

Measure =
VAR __slicer =
    COUNTROWS ( ALLSELECTED ( data[Date] ) )
VAR __date_count =
    COUNTROWS ( ALL ( data[Date] ) )
VAR __measure1 =
    CALCULATE ( [Total Days], ALLEXCEPT ( data, data[Dept] ) )
VAR __measure2 =
    CALCULATE ( [Total Days], ALLEXCEPT ( data, data[Dept], data[Date] ) )
VAR __result =
    IF ( __slicer = __date_count, __measure1, __measure2 )
RETURN
    __result

It will switch the formula for the calculation when there is a selection in the slicer, at which point the code will bring the [Date] column into the internal filter.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group