Numeric slicer: keep blank rows

[epa095]

I have a dataset with a numeric column where some entries have blank/None. I would like to create a numeric **between** slicer and configure it to *keep* the rows with blank. Today it works if one uses "max" slicer, but not a "min" or "between" slicer.

Example dataset:

Name	|Age
John	|43
Mary	|
Roger	|12
Sue	|7

As you see we don't know the age of Mary. I would like to be able to configure the slicer such that she (and other with blank age) shows up no matter what I pick in the slicer. For example between 10 and 30  Is this possible?

[v-yangliu-msft · Accepted solution]

Hi  @epa095 ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 = GENERATESERIES(MIN('Table'[Age]),MAX('Table'[Age]))

2. Create measure.

Flag =
IF(or(MAX('Table'[Age])>=MIN('Table 2'[Value])&&MAX('Table'[Age])<=MAX('Table 2'[Value]),MAX('Table'[Age])=BLANK()),1,0)

3. Place [Flag] in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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

[amitchandak]

@epa095 , Use what of parameter and create a measure like

measure =
var _min = minx(allselected(whatif), whatif[paramter])
var _max = maxx(allselected(whatif), whatif[paramter])
return
calculate(Average(Table[Age]), filter(table, (Table[Age] >= _min && Table[Age] <= _max) || isblank(Table[Age])))

https://docs.microsoft.com/en-us/power-bi/desktop-what-if