What's the best method for toggling data?

[Matty]

Hi Team,

I'm looking for some guidance as to how best acheive the following.

I have table similar to the below:

Date	Report Week
01/01/2018	No
08/01/2018	No
15/01/2018	No
22/01/2018	Yes
29/01/2018	No
05/02/2018	No
12/02/2018	No
19/02/2018	Yes
26/02/2018	No
05/03/2018	No
12/03/2018	No
19/03/2018	No
26/03/2018	Yes

I want a slicer or similar to toggle between showing all data and just the records where 'Yes' exists.  The choice of 'No' should not be possible.

How can this be done?

Cheers,

Matty

[drewlewis15]

Have you thought about using the new bookmark feature?  This would allow you to have an "OFF" view and an "ON" view because you are able to hide the slicer after the data has been sliced.  The following link offers more info: Bookmarks

[Matty]

Hi drewlewis15,

I did think about the bookmark feature, but I wanted to do this programmatically rather than using this feature.  Thanks for the suggestion though!

Cheers,

Matty

[CMAC_Terry]

Hi,

Really easy option would be to set up the slicer, drag report week into the field box and also drag the report week into the report level filter, then use the basic option and only select the yes status;

[Matty]

Hi,

That won't work as applying a report level filter will remove the 'Nos' not just from the slicer, but also from the other visuals, which isn't what I need.

I need a boolean toggle that flicks between 'Yes' and 'All' - I realise this probably will need a measure to achieve, but I can't get my head around how I can do this.

Cheers,

Matty 

[CMAC_Terry]

Hi Matty, 

Not to sure then, perhaps creating a calculated column in an if statement might get something working, e.g 

= if (measure, 1, 0), then change the column type to True or false in the modeling data type, even then you would still have two options in the filter.

Sorry don't know the answer maybe someone out there will.

Terry

[Matty]

Thanks, Terry.

Just to reiterate to other potential helpers in case my original message wasn't clear enough: what I am after is the ability to have something that when ON = only 'Yes' records are shown; when OFF = all records are shown.

Hope someone has a suggestion!

Cheers,

Matty

[thomasronn]

You can create a new table with a single column and only one value "Show only Report weeks". Make a slicer on that field. Now you can toggle on and off "Show only Report weeks".

Now create a measure in your data table

Visible = IF(ISFILTERED(Table2[Column1]),MAX(DataTable[Report Week]) = "Yes",TRUE())

Table2[Column2] is the new single-row table.

Visible will now be true for all rows in datatable if "Show only Report weeks" isn't selected in the slicer and when "Show only Report weeks" is selected only the rows with Yes will be true.

I can't get a filter on Visible to work in visuals when its a boolean, but if you change it to 0 or 1 filters works

Visible = IF( IF( ISFILTERED(Table2[Column1]), MAX(DataTable[Report Week]) = "Yes", TRUE()),1,0)

[Matty]

Hi thomasronn (Thomas?),

I had a similar idea and mocked something up in Excel, but I can't get it to work in Power BI.

It seems that while something like: 

ISFILTERED(Table2[Column1])

Will return TRUE or FALSE to, say, a card visual, but it doesn't work when passed to a calculated column, which is a big shame.

Back to the drawing board it seems...

Cheers,

Matty

[Matty]

Hi,

Reading up a bit more on this, it seems that calculated columns are only refreshed at query time and not at runtime.

This means that one is not able to user a slicer to influence (change) values within a table - a slicer can only act on a table.

Anyone got any other suggestions?

Cheers,

Matty

[thomasronn]

Create a measure on you data table

Visible = IF( IF( ISFILTERED(Table2[Column1]), MAX(DataTable[Report Week]) = "Yes", TRUE()),1,0)

Add Visible to the filters on your visual and set it filtered to 1.

[Matty]

Hi,

Setting the visual filter to 1 removes the dynamic nature of the slicer though, unless I've missed something here?

Cheers,

Matty

[Matty]

Just going to bump this one in case anyone else has any ideas...