Forum Discussion
DAX with multiple filters maybe?
| 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.
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
5 Replies
AnthonyGenoveseResolver III
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!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 resultIf 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!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.
