Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Boolean values / OR condition

I'm trying to create a report that uses multiple boolean fields. The fields though are all related to the same question though. For example, "what colors do you like" would be the overall question for all boolean color fields. So "Blue" is one boolean field, "Red" another boolean field, etc. 

 

We had to separate the answers into multiple boolean field values as our ESP does not allow for multi-select fields. 

 

Now though, I need to report on those boolean values to show, for example, people who like Red OR Blue. 

 

I tried using a slicer on the boolean values but that is applying a filter (editing the interactions didn't help). How can I create a report that will simply allow for total records where the user has indicated Red OR Blue given that Red and Blue are two different boolean fields? 

 

 

Need Help

6 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Could you share a sample in TEXT mode please? The .pbix file would be great. 

     

    Maybe you can try to add a new calculated column like this:

    CALCULATED COLUMN =
IF ( [red] = TRUE (), "Red", IF ( [blue] = TRUE (), "Blue", BLANK () ) )

    Then you can create a slicer from this column.

     

    Best Regards!

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks for trying but I don't see how that will help. I can't share the PBIX file as it contains proprietray info but here's an example

       

       RedBlueYellowGreen
      user1TRUEFALSETRUEFALSE
      user2FALSETRUETRUEFALSE
      user3FALSEFALSEFALSETRUE

       

      All of the columns above relate to the same question - What colors do you like. I want to be able to find users who like Blue OR Yellow. 

       

      This is a small example and we have many more fields associated with the same "Question." 

       

      The other part is that the solution needs to be one that can be made easily available in dashboards for users to work with. I can't expect them to create calculate columns, etc. 

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

         

        There are two methods. You can choose one according to your data model and the size of it.

        1. Try this measure.

        MeasureMethod1 =
SUMX (
    SUMMARIZE (
        'Method1',
        Method1[User],
        "BlueOrYellow", SUMX (
            'Method1',
            IF ( 'Method1'[Blue] = TRUE () || 'Method1'[Yellow] = TRUE (), 1, 0 )
        )
    ),
    [BlueOrYellow]
)

        2. Unpivot the four color columns in the Query Editor, Then try this measure.

        MeasureMethod2 =
CALCULATE (
    DISTINCTCOUNT ( Method2[User] ),
    FILTER (
        'Method2',
        Method2[Attribute] IN { "Blue", "Yellow" }
            && 'Method2'[Value] = TRUE ()
    )
)

        You can see the details in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgS1Zgo00CG9LshPT

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards!

        Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

     

    Best Regards!
    Dale