topic Re: Filter All Loses Slicer Capability in DAX Commands and Tips

Filter All Loses Slicer Capability

Anonymous — Tue, 21 Feb 2023 23:11:37 GMT

I am displaying a table below on my report:

The rows are measures calculated from Table T1. The measure used to calculate the bottom row is:

CALCULATE(sum(T1[Count]), FILTER(

ALL(T1),T1[dateend] >= SELECTEDVALUE('Date'[WeekEndDate]) - 6

&& T1[dateend] <= SELECTEDVALUE('Date'[WeekEndDate]))

)

The purpose of this measure is to only count when dateend from T1 falls within the selected week in the table above.

The problem I am having is that I want to filter by userid in T1. When I selected a value from a slicer which the field is userid, the table above does not change.

What I can do so that my slicer with userid will work with the table above?

Re: Filter All Loses Slicer Capability

olgad — Tue, 21 Feb 2023 23:13:09 GMT

Try Allselected instead of All

Re: Filter All Loses Slicer Capability

FreemanZ — Tue, 21 Feb 2023 23:22:38 GMT

hi Anonymous

try like:

CALCULATE(sum(T1[Count]), FILTER(

ALL(T1[date]),T1[dateend] >= SELECTEDVALUE('Date'[WeekEndDate]) - 6

&& T1[dateend] <= SELECTEDVALUE('Date'[WeekEndDate]))

)

Re: Filter All Loses Slicer Capability

Anonymous — Tue, 21 Feb 2023 23:24:12 GMT

This produced blanks in the table for the bottom row.

Re: Filter All Loses Slicer Capability

Anonymous — Tue, 21 Feb 2023 23:25:04 GMT

This produced blanks in the table for the bottom row.

Re: Filter All Loses Slicer Capability

Anonymous — Mon, 27 Feb 2023 22:16:18 GMT

The answer is to use ALLEXCEPT in place of ALL. Here was the final query that I used.

CALCULATE(sum(T1[Count]), FILTER(

ALLEXCEPT(T1,T1[userid],T1[username]),T1[dateend] >= SELECTEDVALUE('Date'[WeekEndDate]) - 6

&& T1[dateend] <= SELECTEDVALUE('Date'[WeekEndDate]))

)