topic Re: slicer, non selected values in range slicer from a between slicer in DAX Commands and Tips

slicer, non selected values in range slicer from a between slicer

Anonymous — Mon, 24 Apr 2023 07:54:52 GMT

what is the dax query for all values except the selected range

I want to get the value of exp - current which are 2 other measures for the non selected range, 0 to 3(min_hour) and 16 to 23(max_hour) and sum the values for both those non selected ranges

Re: slicer, non selected values in range slicer from a between slicer

johnt75 — Mon, 24 Apr 2023 09:40:14 GMT

Try

Sum outside range = VAR MinHour = MIN ( 'Time'[Hour] ) VAR MaxHour = MAX ( 'Time'[Hour] ) VAR ValueMinHour = CALCULATE ( [My measure], 'Time'[Hour] < MinHour ) VAR ValueMaxHour = CALCULATE ( [My measure], 'Time'[Hour] > MaxHour ) RETURN ValueMinHour + ValueMaxHour

Re: slicer, non selected values in range slicer from a between slicer

Anonymous — Mon, 24 Apr 2023 09:46:29 GMT

It is returning Null...

Re: slicer, non selected values in range slicer from a between slicer

johnt75 — Mon, 24 Apr 2023 09:51:43 GMT

What should it be returning? Are any other slicers or filters affecting the calculation ?

Re: slicer, non selected values in range slicer from a between slicer

Anonymous — Mon, 24 Apr 2023 13:09:31 GMT

it should get the hours from 0 to min, and max to end value 23, and based on that filtered value, I have to caluclate my measure expenditure - current

Re: slicer, non selected values in range slicer from a between slicer

johnt75 — Mon, 24 Apr 2023 13:13:22 GMT

That's what it is supposed to do. The 'Time'[Hour] column is intended to be the column you are using in the slicer.

Re: slicer, non selected values in range slicer from a between slicer

Anonymous — Tue, 25 Apr 2023 05:20:16 GMT

yeah, that is what its supoosed to do, i tried filtering it out also in the same way, but even if the value < start and >end exists, it is returning blak/null, and calculation is not affected by any other slicer

Re: slicer, non selected values in range slicer from a between slicer

johnt75 — Tue, 25 Apr 2023 08:22:36 GMT

Try creating a temporary table like

Tmp table = ADDCOLUMNS ( VALUES ( 'Time'[Hour] ), "My Measure", [My Measure] )

and that should confirm whether the base measure is working or not

Re: slicer, non selected values in range slicer from a between slicer

Anonymous — Tue, 25 Apr 2023 08:43:59 GMT

for this, i am getting for every hour in one column, corresponding measure exp - cur

Re: slicer, non selected values in range slicer from a between slicer

Anonymous — Tue, 25 Apr 2023 08:46:07 GMT

i created a new table of existing and tried filtering out by min val and max val, but it is considering min as 0 and max as 23, that's why its returning blank... it is not taking the slicer min or max when i take it in another measure, but when i put a card and check for minval and maxval it is properly working

Re: slicer, non selected values in range slicer from a between slicer

johnt75 — Tue, 25 Apr 2023 08:49:34 GMT

Use Performance Analyzer to get the DAX code for the visual which isn't working, and paste it into DAX Studio. That will show you which filters are being applied, and will show whether the slicer is affecting it or not. If it isn't then you need to have a look at Edit Interactions to make sure that the slicer values do get passed across.

Re: slicer, non selected values in range slicer from a between slicer

Anonymous — Tue, 25 Apr 2023 09:24:07 GMT

I tried that too, it is getting passed to the required charts too...

Re: slicer, non selected values in range slicer from a between slicer

johnt75 — Tue, 25 Apr 2023 09:28:27 GMT

can you share a PBIX with any confidential info removed ?

Re: slicer, non selected values in range slicer from a between slicer

Anonymous — Sun, 30 Apr 2023 05:38:17 GMT

I am not in the position to share the file, sorry... the min and max of slicer whenever i use in any calculation is giving 0 and 23(start and end) even if the slicer is changed

Re: slicer, non selected values in range slicer from a between slicer

johnt75 — Tue, 02 May 2023 09:18:54 GMT

Are you sure you're doing the MIN / MAX as a measure and not as a calculated column? Measures should definitely be affected by slicers but columns will not be