Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
kfschaefer1
Frequent Visitor

Create a slicer that has a set of default list and 2nd Displays all from a distinct list

my slicer needs to be limited a particular listing of Issues (approx. 6-10 items) on open of the report/dashboard, but the user needs the option to reset the list the full  list of issues (50 items).  Is there a way to create dax that sets the value to the complete listing or turning off the full listing to the limit list via a button? with the Refresh of the data, sets it back to the defualt list?

 

Please point me into the right samples or link on web.

 

Thanks,

KFS

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @kfschaefer1,

One way to do this is (also attached PBIX file):

  1. Add a column "Default Slicer" to the same table that has your slicer column. It can have the values "Yes" or "No". 
  2. Add a filter on your page (or report) on your new column.
  3. Open the Bookmarks Pane. (see below)
  4. Add a Bookmark and call it "All Issues in Slicer". (Check the "Data", "Display", "Current Page", "All Visuals" options.)
  5. Select "Yes" in your filter.
  6. Add a Bookmark and call it "Default Issues Only". (Check the "Data", "Display", "Current Page", "All Visuals" options.)
  7. Add a Blank Button, add text "All Issues", add Action -> Bookmark -> "All Issues in Slicer".
  8. Add a Blank Button, add text "Default Issues", add Action -> Bookmark -> "Default Issues Only".
  9. Open the Selection Pane. (see below)
  10. Hide the "Default Issues" button and Update the "Default Issues Only" bookmark.
  11. Click the "All Issues in Slicer" bookmark.
  12. Hide the "All Issues" button, Un-hide the "Default Issues" button and Update the "All Issues in Slicer" bookmark.
  13. Test the buttons (Note: within Power BI Desktop, you'll need to hold in the Ctrl key to use button navigation.)

 

Defaults with Bookmarks.PNG

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Hi @kfschaefer1,

One way to do this is (also attached PBIX file):

  1. Add a column "Default Slicer" to the same table that has your slicer column. It can have the values "Yes" or "No". 
  2. Add a filter on your page (or report) on your new column.
  3. Open the Bookmarks Pane. (see below)
  4. Add a Bookmark and call it "All Issues in Slicer". (Check the "Data", "Display", "Current Page", "All Visuals" options.)
  5. Select "Yes" in your filter.
  6. Add a Bookmark and call it "Default Issues Only". (Check the "Data", "Display", "Current Page", "All Visuals" options.)
  7. Add a Blank Button, add text "All Issues", add Action -> Bookmark -> "All Issues in Slicer".
  8. Add a Blank Button, add text "Default Issues", add Action -> Bookmark -> "Default Issues Only".
  9. Open the Selection Pane. (see below)
  10. Hide the "Default Issues" button and Update the "Default Issues Only" bookmark.
  11. Click the "All Issues in Slicer" bookmark.
  12. Hide the "All Issues" button, Un-hide the "Default Issues" button and Update the "All Issues in Slicer" bookmark.
  13. Test the buttons (Note: within Power BI Desktop, you'll need to hold in the Ctrl key to use button navigation.)

 

Defaults with Bookmarks.PNG

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson

How do I add a new column, then edit the data to include the default Yes/No?

 

here is my first attempt:

 

DefaultSlicer = 
VAR DefaultYN = 'China Special Handling Request',
		 'Empty Box Received',
		 'Incorrect Product Received',
		 'Lost in Transit',
		'Replacement SN Verification Request',
		'Tamper/Damage Bypass Request',
		'Tamper/Damaged Product Received'
            'Wrong Item Received',
		'SN Dosent Match Ticket'

IssueDefaultSlicerIF('IssueDefaultSlicer'[ProjSubIssueListing]= DefaultYN,"Yes","No")

it does not like the variable, how do i include the list of default choices

Also tried using SWITCH function, but I am getting an error:

 

Function 'SWITCH' does not support comparing values of type True/False with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values

 

DefaultSlicer = 
SWITCH (
    TRUE (),
        'tblProjSubIssueListing'[ProjSubIssueListing]="China Special Handling Request",1,0,
        'tblProjSubIssueListing'[ProjSubIssueListing]="Empty Box Received",1,0,
        'tblProjSubIssueListing'[ProjSubIssueListing]="Incorrect Product Received",1,0,
        'tblProjSubIssueListing'[ProjSubIssueListing]="Lost in Transit",1,0,
        'tblProjSubIssueListing'[ProjSubIssueListing]="Replacement SN Verification Request",1,0,
        'tblProjSubIssueListing'[ProjSubIssueListing]="Tamper/Damage Bypass Request",1,0,
        'tblProjSubIssueListing'[ProjSubIssueListing]="Tamper/Damaged Product Received",1,0,
        'tblProjSubIssueListing'[ProjSubIssueListing]="Wrong Item Received",1,0,
        'tblProjSubIssueListing'[ProjSubIssueListing]="SN Dosen't Match Ticket",1,0
        )  
Anonymous
Not applicable

@kfschaefer1 -

The problem was that you included both 1 and 0 after the condition.

 

The 1st parameter of SWITCH function is the value you are trying to match. The 2nd is some expression. If that expression matches the 1st parameter, then the SWITCH function returns the 3rd parameter. Otherwise, it continues on - if the 4th matches the 1st , return the 5th, and so on. The final parameter is the return value if none of the previous expressions match the 1st parameter.

 

You could also rewrite the function like this, using the column value as the 1st parameter:

DefaultSlicer =
SWITCH (
    'tblProjSubIssueListing'[ProjSubIssueListing],
    "China Special Handling Request", 1,
    "Empty Box Received", 1,
    "Incorrect Product Received", 1,
    "Lost in Transit", 1,
    "Replacement SN Verification Request", 1,
    "Tamper/Damage Bypass Request", 1,
    "Tamper/Damaged Product Received", 1,
    "Wrong Item Received", 1,
    "SN Dosen't Match Ticket", 1,
    0
)

Hope this helps,

Nathan

Thanks for the Dax Switch code, I ended going with a nested IF Statement.

 

I am having a few issues with getting the buttons to work correctly.

 

what I am expecting is to have the slicer update with the appropriate list and also updating the Matrix based on the changes in the slicer.  See Attach pictures.

 

All button

pic1.PNG

 

Default button
pic2.PNG

 

Here are are my current settings.  Is there a way to set a default setting for the Default button on the filter to be set to "YES"?

 

Please help.

 

Anonymous
Not applicable

@kfschaefer1  - Could you share a screenshot of the Relationships between the tables? 

pic3.PNG

Thanks for  your assistance, I am still seeking a resolution for the Slicer/buttons being hidden and change the Slicers data based on the button selected.

Anonymous
Not applicable

@kfschaefer1  - Sorry for the delay. There are several issues, which each need to be addressed:

  1. Slicer/buttons being hidden 
    • This is related to Bookmarks - you can set the Hide/Unhide for each visual in the Selections Pane and then Update your Bookmark.
  2. Change the Slicers data based on the button selected
    • Again, this can be done by making the desired selections and then updating your Bookmark.
      • If you want to use the Default Slicer to limit items in the slicer, make that selection in the Filter Pane. This will persist with the Bookmark, if All Visuals option is selected.
      • If you want to select values within the slicer, make those selections in the slicer.
  3. The Matrix is not updating properly
    • This issue should be fixed by the addressing the steps above (updating the Bookmark with all of the desired selections)

Hope this helps,

Nathan

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.