Boolean values / OR condition

[Anonymous]

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? 

[v-jiascu-msft]

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

[v-jiascu-msft]

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]

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

	Red	Blue	Yellow	Green
user1	TRUE	FALSE	TRUE	FALSE
user2	FALSE	TRUE	TRUE	FALSE
user3	FALSE	FALSE	FALSE	TRUE

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]

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]

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]

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