Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
4 years ago
Solved

Switch between view all or only with values

Must be very simple but cannot get it to work....

 

Have this simple table:

Now I want to make a slicer that shows me only the periods where there are actuals.

Or switch the other side to show also the zeros.

Was trying with If / Calculate and Sum but can only get a Yes (active month) or No (zeros) when I put the calculated field in the vsiual.

But I want it in a slicer.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi rpinxt ,

     

    Sorry for the delay.

    I tried using "SUM ( SAP_Data[ActualsLC] )" instead of "SELECTEDVALUE ( SAP_Data[ActualsLC] )" and it works.

    ActivitySelection = 
VAR selection =
    SELECTEDVALUE ( Activity[Activity] )
RETURN
    SWITCH (
        selection,
        "Yes", IF ( SUM ( SAP_Data[ActualsLC] ) > 0, 1, 0 ),
        "No", IF ( SUM ( SAP_Data[ActualsLC] ) = 0, 1, 0 ),
        1
    )

    This is because the [ActualsLC] here is a sum value and not a single value.

    Pbix as attachment.

     

    Best Regards,

    Jay

19 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

     

    You can create a new column that says if your actuals are Zero or not

    Actuals = If(Table[ActualsLC] = 0, "Zeros","Actuals")
     

     

     
    Then you add your new column to a slicer and you can filter that way
     

     

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    Hi rpinxt ,

    It is simple indeed. 

    1) Add a flag to your table to check if the actuals column has a value or not. If it is 0 then 0 else 1. Name this "ValueFlag"


    2) Add your period and actuals values into a table and add valueflag as a slicer. Set value of valueflag as 1.

     

    When you select 1, it wil show you values

    When you deselect the slicer, it shows all values

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

     

    • rpinxt's avatar
      rpinxt
      Solution Sage
      4 years ago

      This is an interesting one but somehow does not work for me:

      In the table it works.

      But in the slicer nothing shows up.

       

      And when in the filer I cannot put in on advanced filtering.

       

       

      Also..would this only work with 0 and 1?
      Not like with Yes/No or On/Off ?

      • rohit_singh's avatar
        rohit_singh
        Solution Sage
        4 years ago

        Hi rpinxt ,

        Please use the slicer visual that I've circled in red in the screenshot below

         

        You can change the calculated value to show whatever you feel like. I've chosen 0/1. Just be sure to set the filter on the slicer using the same value ( I set it to 1 in my case, you can set it to Yes or On etc depending on the values you choose)

        Kind regards,

        Rohit


        Please mark this answer as the solution if it resolves your issue.
        Appreciate your kudos!  🙂

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi rpinxt ,

     

    Use enter data feature to create a slicer table:

    Then create a measure like below and add it to visual filter.

    Measure =
SWITCH (
    SELECTEDVALUE ( slicer[slicer] ),
    "yes", IF ( SELECTEDVALUE ( 'Table'[ActualsLC] ) > 0, 1, 0 ),
    "no", IF ( SELECTEDVALUE ( 'Table'[ActualsLC] ) = 0, 1, 0 ),
    1
)

     

    Best Regards,

    Jay

    • rpinxt's avatar
      rpinxt
      Solution Sage
      4 years ago

      Very Nice one Anonymous but also this one I cannot not use in a simple slicer apparently....

      What am i doing wrong?!

       

      This is my measure:

      ActivitySelection =
      SWITCH (
      SELECTEDVALUE ( Activity[Activity] ),
      "Yes", IF ( SELECTEDVALUE ( SAP_Data[ActualsLC] ) > 0, 1, 0 ),
      "No", IF ( SELECTEDVALUE ( SAP_Data[ActualsLC] ) = 0, 1, 0 ),
      1
      )
       
      Made this table:

       

      I also cannot put this measure in my filters...just does not take it...

       

      What is different in my measure, table setup??

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi rpinxt ,

         

        The measure should be added to the table visual not the slicer visual.

        If it still doesn't work, please share the pbix file.

         

        Best Regards,

        Jay