Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter table data or Show / Hide based on Slicer Selected value

wynhopkins , jdbuchanan71

 

Hi Guys,

 

I have one dataset which contains one flag column that segregates this dataset into two parts. In my Power BI report there is a slicer for which I've captured the selections using below measure.

 

CaptureRuleTypeSelections = IF(ISFILTERED('OTS NotTestedData'[RuleTypeName]), CONCATENATEX(ALLSELECTED('OTS NotTestedData'[RuleTypeName]), 'OTS NotTestedData'[RuleTypeName], ","), "All")
 

Now my requirement is that when this measure (CaptureRuleTypeSelections) = "All", then show the first part of data else show the another part. So, basically I need to toggle based on that above flag column.

 

Let me know how this could be accomplished using DAX query.

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    Anonymous assuming flag is not a measure but a column, add the following measure and then use it in the visual level filter.

     

    Replace Table[Attribute] with the table and column name you are using in the slicer.

     

     

    Filter Flag = 
VAR __totalRows = CALCULATE ( DISTINCTCOUNT ( 'Table'[Attribute] ), ALL() ) 
VAR __selectedRows = CALCULATE ( DISTINCTCOUNT ( 'Table'[Attribute] ), ALLSELECTED() ) 
VAR __allSelected = __totalRows = __selectedRows
VAR __flag = MAX ( Flag[Flag] )
RETURN
INT (
    IF ( __allSelected, __flag = 0, __flag = 1 ) 
)

     

    Add visual level filter

     

     

    And here is the output

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

     

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous assuming flag is not a measure but a column, add the following measure and then use it in the visual level filter.

     

    Replace Table[Attribute] with the table and column name you are using in the slicer.

     

     

    Filter Flag = 
VAR __totalRows = CALCULATE ( DISTINCTCOUNT ( 'Table'[Attribute] ), ALL() ) 
VAR __selectedRows = CALCULATE ( DISTINCTCOUNT ( 'Table'[Attribute] ), ALLSELECTED() ) 
VAR __allSelected = __totalRows = __selectedRows
VAR __flag = MAX ( Flag[Flag] )
RETURN
INT (
    IF ( __allSelected, __flag = 0, __flag = 1 ) 
)

     

    Add visual level filter

     

     

    And here is the output

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      parry2k ,

       

      Perfect Solution !! Thanks a lot, It's working.

       

      Just want to ask one more thing. Suppose I have 3 slicers in my report like Year slicer and Month Slicer and Rule Name Slicer. Year and Month slicer is configured based on Date dimension table and Rule Name slicer is configured based on Rule dimension table. There is one table visual in report that shows data from Fact NotTested Table.

       

      Now my requirement is if user selects "SELECT ALL" or Nothing (which means all is selected by default) in Rule Name slicer then relationship between Date Dimension table and Fact NotTested Table should be based on Year column. In this case Month slicer would not work and "SELECT ALL" would be there by default in month slicer.

      But when any single or multiple values are selected in Rule Name slicer then relationship between Date Dimension table and Fact NotTested Table should be based on Month column. That means in this case Month slicer would work.

       

      Note: Rule Dimension table already have a relationship with Fact NotTested table. Let me know how we can switch relationships as per above scenario using a measure.

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous can you put sample data and expected output in a pbix and share thru one drive/google drive.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      parry2k, mahoneypat , wynhopkins 

      Guys please find sample data as below -

       

      ID     Name     Flag

      1         A            1

      2         B            1

      3         C            0

      4         D            0

       

      Let me make my requirement straight-forward for you. I have a slicer in power bi report which is configured based on different field, from which if user selects a single value or multiple values then table should display records having flag = 1. Refer below output.

       

      ID     Name    Flag

      1        A           1

      2        B           1

       

      Now if user selects, "SELECT ALL" from slicer or nothing (which means all values are selected by default), only then table should display records having flag = 0. Refer below output.

       

      ID     Name    Flag

      3        C           0

      4        D           0

       

      Hope this explanation helps. Kindly let me know how this could be accomplished. I am looking towards some sort of measure that can be used to filter or show-hide the data based on slicer selections..