Forum Discussion

Wiene24's avatar
Wiene24
Icon for Helper II rankHelper II
7 years ago
Solved

Smart Slicer

Hi,   Is it possible to make a slicer smarter?   I have a database with clients and with invoice amounts.   I have a slicer with years, but now I want that the slicer where I can select a certa...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Wiene24,

     

    The custom visual "HierarchySlicer" is the solution. It has a Value filed where we can add a measure to filter the values. Please refer to the demo below. Some items are filtered out by the measure. 

    SlicerOnly =
VAR temp =
    SUM ( Sales[SalesQuantity] )
RETURN
    IF ( temp >= 100000, temp, BLANK () )

    Smart-Slicer2

     

    Best Regards,
    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Tim,

 

There is a workaround. Add a Calculated Column to the table where the client is from. For example, if there aren't any sales in the [Sales] table, the column will be blanks. 

Column =
IF (
    ISBLANK ( SUMX ( RELATEDTABLE ( Sales ), [SalesQuantity] ) ),
    BLANK (),
    [ColorName]
)

Smart-Slicer

 

 

Best Regards,
Dale

Wiene24's avatar
Wiene24
Icon for Helper II rankHelper II
7 years ago

Hi Dale,

 

The problem is that there is data in the sales table from almost all clients, but I want that if I select 2017 that only the clients that had sales in 2017 will show. If I then change it to 2018 I only want to see the clients that had sales in 2018.

 

Because we have alot of one time sales they will now always stay in my slicer.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Wiene24,

     

    The custom visual "HierarchySlicer" is the solution. It has a Value filed where we can add a measure to filter the values. Please refer to the demo below. Some items are filtered out by the measure. 

    SlicerOnly =
VAR temp =
    SUM ( Sales[SalesQuantity] )
RETURN
    IF ( temp >= 100000, temp, BLANK () )

    Smart-Slicer2

     

    Best Regards,
    Dale

    • Wiene24's avatar
      Wiene24
      Icon for Helper II rankHelper II
      7 years ago

      Thanks Dale that is the perfect slicer!

       

      Kind regards,

       

      Tim

    • Wiene24's avatar
      Wiene24
      Icon for Helper II rankHelper II
      7 years ago

      Hi,

       

      When I first started using this slicer it worked even without the Value field.

      But I think something changed that it doesn't work anymore, for any of my reports.

      I didnt look at the measure yet because I didn't need it. I cant use the VAR temp and Return. They just don't excist.

       

      Is there any other way I can make the slicer that smart that it only shows values that are active in the report?