Exclude Records Using Yes/No Slicer

[jbhca12]

Howdy,

I have a single table with several columns, one of which is called oeeTime.

My report is a machine downtime report, which means I only want to see records/rows of when the machine is not running.

The oeeTime values are 1, 2 or 3. 2 is "up time", so I don't want to see 2. 1 and 3 indicated downtime. 1 means it was un-scheduled downtime and 3 means it was scheduled downtime. So here is what I need...

I'd like to have a "Yes/No" slicer that simply asks the user, "Do you want to include scheduled downtime?" Yes or No.

If the user selects "Yes", then include records with oeeTime = 3 (so oeeTime =1 AND oeeTime = 3), if "No", only display records with oeeTime = 1.

For those records that contain an oeeTime = 1 or oeeTime = 3, I do have a calculated "Planned Downtime" column that is "Yes" or "No" and I'm presently using that column for the Planned Downtime slicer.

This works OK as based on the above, it filters out any records with oeeTime = 3 (oeeTime = 1 only) and if you either "Select all" or ensure that both "No" and "Yes" are selected (or nothing is selected) then it will include both oeeTime = 1 and oeeTime = 3.

I'd prefer it look like this....

...as to me that is more intuitive for the user, but this behaves such that "No" excludes oeeTime = 3 but if you select "Yes", it only gives you records with oeeTime = 3 and excludes oeeTime = 1, not what I want.

Not sure how best to do this?

Thanks!

[v-xulin-mstf · Accepted solution]

Hi @jbhca12

Try measure as:

Measure = 
var _slicer= SELECTEDVALUE(Slicer[Include scheduled downtime?])
return
IF(
    _slicer="Yes",
    IF(
        MAX('Table'[OneTime])=3 || MAX('Table'[OneTime])=1,
        1,
        0),
    IF(
        MAX('Table'[OneTime])=1,
        1,
        0
    )
)

The pbix is attached.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[amitchandak]

@jbhca12 , you need to create a new column and use that

if([oeeTime] =2, "No", "Yes")

[jbhca12]

I don't think you understood my explaination. oeeTime = 2 is already filtered out...I need to be able to "Yes" or "No" concerning downtime of oeeTime = 1 or oeeTime = 3. I simply want a "Yes" or "No" so the records are oeeTime = 1 only or (oeeTime = 1 AND oeeTime = 3).

Thanks!

[amitchandak]

@jbhca12 , Those screenshots did not appear when I saw last time. Loading issue.

Let me make one more attempt

Measure =

var _sel = selectedvalue(slicer[slicer]) //Yes and no slicer

return

if(_sel ="Yes", calculate(sum(Table[value]), filter(Table, Table[oeeTime] in {1,3})) , calculate(sum(Table[value]), filter(Table, Table[oeeTime] in {1})))

for add 2

Measure =

var _sel = selectedvalue(slicer[slicer]) //Yes and no slicer

return

if(_sel ="Yes", calculate(sum(Table[value]), filter(Table, Table[oeeTime] in {1,2,3})) , calculate(sum(Table[value]), filter(Table, Table[oeeTime] in {1,2})))

[jbhca12]

Not sure I understand. Where am I creating this measure? Also, you wrote:

"var _sel = selectedvalue(slicer[slicer])" - How do you reference a slicer?

...and what are you summing as a Table[value]?

Sorry, I think you'll need to dumb this down a bit...I'm not understanding what you are suggesting.