Forum Discussion
Numeric slicer: keep blank rows
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?
Hi Anonymous ,
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
2 Replies
Anonymous , 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])))Hi Anonymous ,
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
