topic Re: median -- ignore Slicer Selection in DAX Commands and Tips

median -- ignore Slicer Selection

carlol — Thu, 11 Nov 2021 20:12:48 GMT

this Measure get me the median based on Slicer Selection, Country

IF ( ROUND(MEDIAN(vwKPIMedianDoorToCT[Minutes]),1) = BLANK (), 0, ROUND(MEDIAN(vwKPIMedianDoorToCT[Minutes]),1) )

I want to ignore the country , and also return the median for all countries , for comparison

?? --

ROUND(MEDIAN( Calculate(vwKPIMedianDoorToCT[Minutes],ALLSELECTED ) ),1)

thanks

Re: median -- ignore Slicer Selection

AlexisOlson — Thu, 11 Nov 2021 20:29:50 GMT

I'd try writing it like this:

VAR MedianAllCountries = CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ), ALLSELECTED () ) RETURN IF ( ISBLANK ( MedianAllCountries ), 0, ROUND ( MedianAllCountries, 1 ) )

Re: median -- ignore Slicer Selection

carlol — Thu, 11 Nov 2021 20:40:31 GMT

Great thanks

If I want to ignore all slicer but the Date dimension , having issues with e[Quarter]))) brackets

VAR MedianNational =

CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ),FILTER(ALLEXCEPT(DimDate,DimDate[CurrentYear],DimDate[Quarter])))

RETURN

IF ( ISBLANK ( MedianNational ), 0, ROUND ( MedianNational , 1 ) )

Re: median -- ignore Slicer Selection

AlexisOlson — Thu, 11 Nov 2021 20:45:18 GMT

You can add back in the tables you want to keep as filter context like this:

CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ), ALLSELECTED (), DimDate )

Alternatively, use ALLSELECTED more narrowly only on the things you want to remove local filter context for. For example,

CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ), ALLSELECTED ( Slicer[Country] ) )

Re: median -- ignore Slicer Selection

carlol — Thu, 11 Nov 2021 20:48:29 GMT

Great thanks

Looks simple when written !

Re: median -- ignore Slicer Selection

carlol — Thu, 11 Nov 2021 20:49:28 GMT

thanks

Looks simple when coded

Re: median -- ignore Slicer Selection

carlol — Thu, 11 Nov 2021 20:58:35 GMT

This does not appear to be working ,

If I select all countries , 2021 Q1 Median is 75

If I select 1 country , (I want to igore this slicer) , it is 30 , ( I want to illustrate 75 regardless of country selection) but to still filter based on Date

CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ),ALLSELECTED (), DimDate)

Re: median -- ignore Slicer Selection

v-robertq-msft — Mon, 15 Nov 2021 02:31:46 GMT

Hi,

According to your description, I can roughly understand your requirement, you can try this measure:

Median = var _value=MEDIANX(ALL(vwKPIMedianDoorToCT),[Minutes]) return IF(_value=BLANK(),0,ROUND(_value,1))

Then you can create a slicer and a card chart to place this measure, and you will find that the measure will always display the value of the total median regardless of the selection of the slicer:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: median -- ignore Slicer Selection

AlexisOlson — Mon, 15 Nov 2021 15:46:52 GMT

If you want to ignore a slicer, then you need to use ALL instead of ALLSELECTED.

See if this is any closer:

CALCULATE ( MEDIAN ( vwKPIMedianDoorToCT[Minutes] ), ALL ( vwKPIMedianDoorToCT ), DimDate )