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,

 

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