Check if all values in category are positive/negative

[Fiejj]

Hi everyone,

I'm struggling with this small problem which is probably easy to solve. I'm working in DirectQuery mode. Let's say I have several categories each having multiple values:

Cat A           0.47
                    0.48

                  -0.37

                  -0.74

Cat B            0.85

                    0.67

                    0.84

                    0.63

I want Power BI to check these four values and return for example '1' for the particular category if all four values are positive, and '-1' if all four values are negative. Otherwise it should return '0'. Is this possible using DAX? I have tried SUM (sum should be 4 or -4), AVERAGE and the SIGN function, but no success so far. I have no experience using queries/SQL by the way.

Thanks!

[Phil_Seamark · Accepted solution]

Hi @Fiejj

I created the following table in SQL and connected to it using Direct Query.

Cat	n
Cat A	0.47
Cat A	0.48
Cat A	-0.37
Cat A	-0.74
Cat B	0.85
Cat B	0.67
Cat B	0.84
Cat B	0.63

I then added the following calulated column to that table in Power BI

Column = if('N'[n]<0,1,2)

This meant I could now create a measure to test if all the values in the column were the same for each category

Is Same = 
var MaxValue = CALCULATE(MAX('N'[Column]),ALLEXCEPT('N',N[Cat]))
var MinValue = CALCULATE(MIN('N'[Column]),ALLEXCEPT('N',N[Cat]))
var Result = IF(MaxValue=MinValue,"All Same","Different")
Return Result

How does that look?

[Fiejj]

Thanks everyone, it worked, totally forgot to reply but really appreciate your help!

[v-yulgu-msft · Accepted solution]

Hi @Fiejj,

In your scenario, you can first create a calculated column:

flag =
IF ( testpositivenegative[value] > 0, 1, 0 )

Then, create a measure to check if all values in category are positive/negative and show corresponding value.

Count =
IF (
    CALCULATE (
        SUM ( testpositivenegative[flag] ),
        ALLEXCEPT ( testpositivenegative, testpositivenegative[Category] )
    )
        = 4,
    1,
    IF (
        CALCULATE (
            SUM ( testpositivenegative[flag] ),
            ALLEXCEPT ( testpositivenegative, testpositivenegative[Category] )
        )
            = -4,
        -1,
        0
    )
)

Regards,
Yuliana Gu

[Phil_Seamark · Accepted solution]

Hi @Fiejj

I created the following table in SQL and connected to it using Direct Query.

Cat	n
Cat A	0.47
Cat A	0.48
Cat A	-0.37
Cat A	-0.74
Cat B	0.85
Cat B	0.67
Cat B	0.84
Cat B	0.63

I then added the following calulated column to that table in Power BI

Column = if('N'[n]<0,1,2)

This meant I could now create a measure to test if all the values in the column were the same for each category

Is Same = 
var MaxValue = CALCULATE(MAX('N'[Column]),ALLEXCEPT('N',N[Cat]))
var MinValue = CALCULATE(MIN('N'[Column]),ALLEXCEPT('N',N[Cat]))
var Result = IF(MaxValue=MinValue,"All Same","Different")
Return Result

How does that look?