DAX Help

[redomer44]

CFQ1 =
//Better than baseline AND target
if(search("N/A",max('_FY25 CFG Performance OG'[as of 9/30/23]),1,0) <> 0 ,0,
IF(
max('_FY25 CFG Performance OG'[as of 9/30/23 - Copy]) >= max('_FY25 CFG Performance OG'[Baseline] )
&& max('_FY25 CFG Performance OG'[as of 9/30/23 - Copy]) >= max('_FY25 CFG Performance OG'[FY 24Target]),
3,
//Better than baseline BUT NOT target
IF(
max('_FY25 CFG Performance OG'[as of 9/30/23 - Copy]) >= max('_FY25 CFG Performance OG'[Baseline])
&& max('_FY25 CFG Performance OG'[as of 9/30/23 - Copy]) < max('_FY25 CFG Performance OG'[FY 24Target]),
2,
//Worse than baseline and target
IF(
max('_FY25 CFG Performance OG'[as of 9/30/23 - Copy]) < max('_FY25 CFG Performance OG'[Baseline] )
&& max('_FY25 CFG Performance OG'[as of 9/30/23 - Copy]) < max('_FY25 CFG Performance OG'[FY 24Target]),
1,
//N/A this quarter
0
))))

I’ve created conditional logic for Better than baseline AND target, Better than baseline BUT NOT target, and Worse than baseline and target. Now, I need to count the occurrences of each of these categories.

I create below measure for BetterThanBaseline&Target  and it return blank()

BetterThanBaseline&Target =
CALCULATE(
COUNTROWS('_FY25 CFG Performance OG'),
FILTER(
'_FY25 CFG Performance OG',
max( '_FY25 CFG Performance OG'[as of 9/30/23 - Copy]) >= '_FY25 CFG Performance OG'[Baseline] &&
max( '_FY25 CFG Performance OG'[as of 9/30/23 - Copy]) >= '_FY25 CFG Performance OG'[FY 24Target]
)
)

[rajendraongole1 · Accepted solution]

Hi @redomer44 - CALCULATE with FILTER and COUNTROWS in the way you did won’t work as expected because CALCULATE does not interact well with MAX in the FILTER context like this. Instead, you should use a pattern that applies the logic directly in the measure without the FILTER function.

check the below measure for better than baseline AND target

BetterThanBaselineAndTargetCount =
CALCULATE(
COUNTROWS('_FY25 CFG Performance OG'),
'_FY25 CFG Performance OG'[as of 9/30/23 - Copy] >= '_FY25 CFG Performance OG'[Baseline],
'_FY25 CFG Performance OG'[as of 9/30/23 - Copy] >= '_FY25 CFG Performance OG'[FY 24Target]
)

another measure for better than baseline BUT NOT target 

BetterThanBaselineNotTargetCount =
CALCULATE(
COUNTROWS('_FY25 CFG Performance OG'),
'_FY25 CFG Performance OG'[as of 9/30/23 - Copy] >= '_FY25 CFG Performance OG'[Baseline],
'_FY25 CFG Performance OG'[as of 9/30/23 - Copy] < '_FY25 CFG Performance OG'[FY 24Target]
)

worse than baseline and target create another measure:

WorseThanBaselineAndTargetCount =
CALCULATE(
COUNTROWS('_FY25 CFG Performance OG'),
'_FY25 CFG Performance OG'[as of 9/30/23 - Copy] < '_FY25 CFG Performance OG'[Baseline],
'_FY25 CFG Performance OG'[as of 9/30/23 - Copy] < '_FY25 CFG Performance OG'[FY 24Target]
)

check the above logics.