Need help with a measure to check multiple rows for a condition...

[bstock]

I have the following data in a table:

UserName      isValid

A                      yes

A                      no

A                      yes

B                       yes

B                       yes

B                       yes

I would like a measure that would respond  "NOT VALID" for A and "VALID" for B.  Basically, any of the "isValid" = no, then "NOT VALID"....

I hope I'm asking this right....

[Anonymous]

Hi @bstock ,

Please use below measure to achive this request.

Measure:
Is_Valid = IF(
COUNTROWS(
FILTER('Sample','Sample'[isValid]="No")
)>0,"Not Valid"
,"Valid")

Output:


Best Regards,
Mail2inba4

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lionel-msft]

Hi @bstock ,

Column = 
SWITCH(
    Sheet12[isVaild],
    "yes", "vaild",
    "no", "Not vaild"
)

Column 2 = 
IF(
    Sheet12[Name] = "A" && Sheet12[isVaild] = "no"  || (Sheet12[Name] = "B" && Sheet12[isVaild] = "yes"),
    "XXX",
    BLANK()
)

Measure1 = 
VAR x=
CALCULATE(
    COUNT(Sheet12[isVaild]),
    FILTER(
        Sheet12,
        Sheet12[Name] = "A" && Sheet12[Column] = "Not vaild"
    )
)
VAR y=
CALCULATE(
    COUNT(Sheet12[isVaild]),
    FILTER(
        Sheet12,
        Sheet12[Name] = "B" && Sheet12[Column] = "vaild"
    )
)
RETURN
IF(
    x >0 || y > 0,
    COUNT(Sheet12[isVaild])
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bstock]

 Ok, so I came up with this:

It seems to work as I would have wanted, just curious if there is maybe a better or more flexible way to accomplish this? 

isQualified = 
if (
    COUNTROWS(
        FILTER(
            userQuals,
            userQuals[IsQualified] = "FALSE"
        )
    ) > 0,
    "FALSE", "TRUE"
)​

[Anonymous]

Instead of COUNTROWS > 0 you could also use ISEMPTY. If your table is large, this might be more efficient as the lines aren't actually counted this way. Like this:

IF ( 

    ISEMPTY (

        FILTER(
            userQuals,
            userQuals[IsQualified] = "FALSE"
        )
    ),

    "TRUE",
    "FALSE"

)