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 fo...
Need Help
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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi there,

      Thanks so much for trying to help. I've been trying to see how I can make this work. The issue is that fields I would be working with are about 10 - 12 different fields. So picture 10 more colors. Ultimately, this would be distributed to sales, etc. who would be able to filter freely to see what the totals are in the categorie(s) they select. With so many fields, I can't account for every possible combination of fields they could make and my coworkers need to be able to do it on their own easily w/out creating measures. Unless I'm missing something?

       

      I'm actually a little surprised that being able to use "OR" versus "AND" is not already baked into PowerBI. 

       

      Any other ideas? I really do appreciate all the help. 

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

        Hi Anonymous,

         

        Did you try the method 2?

        You can try this formula that allows you to select the colors from a slicer.

         

        MeasureMethod2 =
VAR chosenColors =
    ALLSELECTED ( Method2[Attribute] )
RETURN
    IF (
        ISFILTERED ( Method2[Attribute] ),
        CALCULATE (
            DISTINCTCOUNT ( Method2[User] ),
            FILTER (
                'Method2',
                Method2[Attribute] IN chosenColors
                    && 'Method2'[Value] = TRUE ()
            )
        ),
        BLANK ()
    )

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards!

        Dale