Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

slicer with yes and No

i have the data as below   id    type 1     fruit 2     vegetable 3     fruit 4     fruit 5     vegetable   i am looking for slicer filter with display yes and no option.  Yes - all records...
Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    You can create a measure and add it to visual level filter as below:

     

    if show = IF(SELECTEDVALUE('slicer table'[slicer])="yes",1,IF(MAX('Table'[ type])<>"vegetable",1))

     

    Or create measures and add it the column chart

    Measure =
SWITCH (
    MAX ( 'new table'[slicer] ),
    "no", CALCULATE (
        DISTINCTCOUNT ( 'Table'[id] ),
        FILTER (
            'Table',
            'Table'[ type] <> "vegetable"
        )
    ),
    "yes", DISTINCTCOUNT ( 'Table'[id] )
)

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Assuming you have a disconnected table called Slicer (e.g., use Enter Data to make a table with Yes and No, and have no relationships to other tables), you can use a measure like this to get your desired result:

 

SlicerCount = if(selectedvalue(Slicer[Decision]) = "Yes", countrows(Data), Calculate(countrows(Data), Data[Type]="Fruit"))

 

This also assumed your table is called "Data" and the Slicer column is called "Decision".

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Here are the steps i followed.

     

    1. Created a table  with Slicer and column Selection with roes Yes and No.

    2. Created a measure under table SlicerCount with the formula you provided.

    3.added a Visual Slicer for selection.

    4. when i click Yes and No . row counts are same.

     

    Please let me know where i missed.

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous 

      Step 5

      5. Place the measure in the visual filters for the table visual and choose to show if result is 1

      https://www.sqlbi.com/articles/applying-a-measure-filter-in-power-bi/

      Please mark the question solved when done and consider giving kudos if posts are helpful.

      Contact me privately for support with any larger-scale BI needs, tutoring, etc.

      Cheers 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks for your patienace.

         

         

         

         

        expecting like yes and no. i am able to see only graph. and able to choose to show if result is 1 (where should i add this)