DAX Help

redomer44 — Sun, 03 Nov 2024 03:02:51 GMT

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]
)
)

Re: DAX Help

rajendraongole1 — Sun, 03 Nov 2024 03:28:52 GMT

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.

topic DAX Help in DAX Commands and Tips

DAX Help

Re: DAX Help