Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
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
@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