by Hi All,
Today we are going to see a small use case but powerful condition. There is a value column used in the slicer. The slicer drop-down shows list of values. Now they want to slice the data where the value greater than Zero. Well that is very quick right. Here is the tricky part they want the drop-down as greater than Zero and All. The All condition includes Zero.
Sounds like a easy one but if you try to give the condition in calculated column or in power query there is one case only pass.
Lets say you wrote the DAX or condition column in Power Query.
IF(SUM(Column) > 0, “> 0”, “ALL”)
If you use the above created column in the slicer there is two drop-down shows
0
ALL
So is this right? Absolutely not
Lets check the value by clicking > 0 filter yes it worked fine.
Now lets click ALL it shows only the value as Zero.
We can multi select both the dropdown
The user wants to see the >0 and Including 0.
Well the ask is not mathematical but sometimes the set values column in the slicer that have positive negative and zero value, its not methodical way but the end users requested. So lets do this
Go and create the conditional column in Power Query or Calculated column in PBI
IF(SUM(Column) > 0, “> 0”) or Calculated Column IF(SUM(Column) > 0, “> 0”)
Else condition will be automatically came as null if we not given anything,
Now lets bring this column in the slicer and select the Select all option in the slicer settings.
Now select the slicer and make visual level filter as is not Blank.
Now we have only two option
Select all
> 0
If you select > 0 it shows values only greater than Zero.
If you select Select all then it shows the values greater than Zero as well as equal to Zero.
Yes, understood this is not the only solution. But this is the quickest and easiest solution.
Thanks, see you in the next use case.
