DAX with multiple filters maybe?

[bacondrops]

Group	Application	Compatibility		Result
Retirement	Cash Cow 2.0	Yes		No
Retirement	Blue Hair 3.1	No
Investments	Cash Cow 2.0	Yes		No
Investments	Blue Hair 3.1	No
Investments	Doge Coin 1.9	No
Investments	Wall Street 5.5	No
Real Estate	Zillow 9.4	Yes		Yes
Real Estate	Sell It All 1.0	Yes
Real Estate	Crash 1.1	Yes
Insurance	Cash Cow 2.0	Yes		Yes
Insurance	Sell It All 1.0	Yes
Insurance	Existence 2.2	Yes
Insurance	No Lizards 4.9	Yes

So, I have groups that contain different applications within each group.  And each application is marked as compatible (with something else outside this data set).  What I need to do is find out which Group has ALL of it's applications marked with a yes, and which Group has at least 1 no in it and spit those two results out into another column because if there's at least one application within a group that is NOT compatible, then I don't want to mark that Group as compatible.  The Result column above is what I'm looking for.  Hopefully that's clear, I feel like I stumbled through that explanation.  

[bacondrops]

Yes, this will be used in a table visual. 

[AnthonyGenovese]

It's hard to give an exact dax calculation without knowing the exact tables and columns used, and what filters will be active on the report. But I would do something like this. Basically, count the rows in the group, removing all the filters on the same table except compatability, and then filter out Yes & No
Result Measure =

Var YesCnt = calculate(DistinctCount('TableName'[Group]), All('TableName'[Compatability]), 'TableName'[Compatability]="YES")
Var NoCnt= IsBlank(calculate(DistinctCount('TableName'[Group]), All('TableName'[Compatability]), 'TableName'[Compatability]="No")
var noBlankYes=if(isblank(yesCnt),0,yesCnt)

var noBlankNo=if(isblank(noCnt),0,noCnt)
var result = if(and(noBlankYes>0 ,noBlankNo=0),"YES","NO")
return result

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!

[bacondrops]

It's giving me the red squiggly lines on the last 3 variables - Var noBlankYes, Var noBlankNo, and Var result, as well as the Return result line.  It's an Unexpected Expression Var.

[Anonymous · Accepted solution]

Hi @bacondrops ,

Please check if you use the correct table name in the formula. And you can create a calculated column or measure as below to get it, please find the details in the attachment.

Calculated column:

Result = 
VAR _countyes =
    CALCULATE (
        COUNT ( 'Table'[Application] ),
        FILTER (
            'Table',
            'Table'[Group] = EARLIER ( 'Table'[Group] )
                && 'Table'[Compatibility] = "Yes"
        )
    )
VAR _countall =
    CALCULATE (
        COUNT ( 'Table'[Application] ),
        FILTER ( 'Table', 'Table'[Group] = EARLIER ( 'Table'[Group] ) )
    )
RETURN
    IF ( _countyes = _countall, "Yes", "No" )

Measure:

Measure = 
VAR _group =
    SELECTEDVALUE ( 'Table'[Group] )
VAR _countyes =
    CALCULATE (
        COUNT ( 'Table'[Application] ),
        FILTER ( 'Table', 'Table'[Group] = _group && 'Table'[Compatibility] = "Yes" )
    )
VAR _countall =
    CALCULATE (
        COUNT ( 'Table'[Application] ),
        FILTER ( 'Table', 'Table'[Group] = _group )
    )
RETURN
    IF ( _countyes = _countall, "Yes", "No" )

Best Regards

[AnthonyGenovese]

I understand how result is caluclated. But how do you want the final result to look like? Will this be a table visual? Only showing Group and Result?

If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!