topic Re: Two Slicers in one Page How to use All or Allexcept or Allselected properly in DAX Commands and Tips

Two Slicers in one Page How to use All or Allexcept or Allselected properly

MichelleQLi — Thu, 23 Jun 2022 23:32:00 GMT

Dear Experts,

I have a fact table "FT-Mitre10" (plese see below picture/snapshot for details).

My Power BI sheet has two filters on the same page:

- Category

- Rate Captured (which is Date)

I just feel confused how to use filter funtion to make two filters working at the same time.

Below is my current failed funtions:

M91 = CALCULATE(AVERAGE('FT-Mitre10'[Rate]), FILTER(ALLSELECTED('FT-Mitre10'[Category]), MIN('FT-Mitre10'[Rate Captured])))
M92 = CALCULATE(AVERAGE('FT-Mitre10'[Rate]), FILTER('FT-Mitre10', 'FT-Mitre10'[Rate Captured]= MIN('FT-Mitre10'[Rate Captured])))
M93 = CALCULATE(AVERAGE('FT-Mitre10'[Rate]), FILTER(ALL('FT-Mitre10'), 'FT-Mitre10'[Rate Captured] = MIN('FT-Mitre10'[Rate Captured])))
M94 = CALCULATE('FT-Mitre10'[M91], FILTER(ALL('FT-Mitre10'), 'FT-Mitre10'[Rate Captured] = MIN('FT-Mitre10'[Rate Captured])))

My Expectation is:

M91 is a fixed rate, showing single item rate on the earliest date (which is 2022/03/18 rate in this case).

Won't be affected by Date filters. But currently, it changes when I select on different date.

I tried to use All function to fixed date, which is my current M93.

However, it makes my rate no longer single item rate but an average rate of all items, which is not what I expected.

I want a single item rate but the date is the earliest date of All Table rather than earliest date of Date Filter.

M94 is me tried to use two nested filter to get rid of ALL function, I thought M91 result gonna be single item rate, then I can use ALL only to control date filter. But failed, seems ALL still will control all....

M92 is a non-fixed rate, showing single item rate on the earliest selected date.

Will be affected by the Date filter.

Will be affected by the Category filter.

Currently, it seems working fine as I expected. But I don't get the difference of my current M91 and M92.

Under what circumstances those two measures will show different result?

Re: Two Slicers in one Page How to use All or Allexcept or Allselected properly

amitchandak — Fri, 24 Jun 2022 13:51:53 GMT

MichelleQLi , Based on what I got

M91 =
Var _min = minx(all('FT-Mitre10') , 'FT-Mitre10'[Rate Captured])
return
CALCULATE(AVERAGE('FT-Mitre10'[Rate]), FILTER(all('FT-Mitre10'), 'FT-Mitre10'[Rate Captured] =_min ))

M92 =
Var _min = minx(allselected('FT-Mitre10') , 'FT-Mitre10'[Rate Captured])
return
CALCULATE(AVERAGE('FT-Mitre10'[Rate]), FILTER('FT-Mitre10', 'FT-Mitre10'[Rate Captured] =_min ))

Re: Two Slicers in one Page How to use All or Allexcept or Allselected properly

MichelleQLi — Sun, 26 Jun 2022 09:11:43 GMT

Hi ,

Big thanks for your M92 which is more concise and clear than mine. I totally accept your solution.

Ngā mihi | Kind Regards,

Michelle Q Li

Re: Two Slicers in one Page How to use All or Allexcept or Allselected properly

MichelleQLi — Sun, 26 Jun 2022 09:15:54 GMT

Hi Amit,

Regarding the M91, I tried your solution, it doesn't work well as I believe it is the ALL function still holding highest priority and calculate all information from the table. However I have made a M91-5 to work it out. Please see below snapshot for more details.

Re: Two Slicers in one Page How to use All or Allexcept or Allselected properly

MichelleQLi — Sun, 26 Jun 2022 09:16:35 GMT

Re: Two Slicers in one Page How to use All or Allexcept or Allselected properly

MichelleQLi — Sun, 26 Jun 2022 09:17:20 GMT

Re: Two Slicers in one Page How to use All or Allexcept or Allselected properly

MichelleQLi — Sun, 26 Jun 2022 09:18:58 GMT

Re: Two Slicers in one Page How to use All or Allexcept or Allselected properly

powerbi_zone — Wed, 21 Feb 2024 02:24:00 GMT

We need to clearly understand the diffrence between ALL ,ALLSELECTED and ALLEXCEPT and then how to use them .

If i try to explain everything in this blog post it comes too lengthy .Found one of the best videos which clearly gives the difference with a single example that clears all the confusion .Hope this helps .Cheers !

https://www.youtube.com/watch?v=aVVua4fG1V0