Forum Discussion
DAX Help
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]
)
)
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.
1 Reply
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.