Forum Discussion
NewbieJono
Post Partisan
Post PartisanReturn 0 if blank
hello all, prob an easy one for someone, how can i get this code to return 0 if blank
NEW RAG =
VAR __workStream = 'FACT - Backlog'[Grouping]
VAR __daysOld = 'FACT - Backlog'[Days Old]
RETURN
CALCULATE (
MAX ( 'RAG MATRIX (TESTING)'[RAG] ),
'RAG MATRIX (TESTING)'[Workstream] = __workStream,
__daysOld >= 'RAG MATRIX (TESTING)'[Min Range],
__daysOld <= 'RAG MATRIX (TESTING)'[Max Range]
)Hi NewbieJono ,
Please modify the formula like this:
NEW RAG = VAR __workStream = 'FACT - Backlog'[Grouping] VAR __daysOld = 'FACT - Backlog'[Days Old] RETURN CALCULATE ( MAX ( 'RAG MATRIX (TESTING)'[RAG] ), 'RAG MATRIX (TESTING)'[Workstream] = __workStream, __daysOld >= 'RAG MATRIX (TESTING)'[Min Range], __daysOld <= 'RAG MATRIX (TESTING)'[Max Range] ) + 0Or like this:
NEW RAG = VAR __workStream = 'FACT - Backlog'[Grouping] VAR __daysOld = 'FACT - Backlog'[Days Old] VAR __result = CALCULATE ( MAX ( 'RAG MATRIX (TESTING)'[RAG] ), 'RAG MATRIX (TESTING)'[Workstream] = __workStream, __daysOld >= 'RAG MATRIX (TESTING)'[Min Range], __daysOld <= 'RAG MATRIX (TESTING)'[Max Range] ) RETURN IF ( __result = BLANK (), 0, __result )Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
PaulDBrownCommunity Champion
Either add + 0 at the end of the measure or:
0 for blanks = COALESCE ([NEW RAG] , 0)
NewbieJonosorry at which point do i put the 0 ?
which is the preferred method ?
v-yanjiang-msftCommunity Support
Hi NewbieJono ,
Please modify the formula like this:
NEW RAG = VAR __workStream = 'FACT - Backlog'[Grouping] VAR __daysOld = 'FACT - Backlog'[Days Old] RETURN CALCULATE ( MAX ( 'RAG MATRIX (TESTING)'[RAG] ), 'RAG MATRIX (TESTING)'[Workstream] = __workStream, __daysOld >= 'RAG MATRIX (TESTING)'[Min Range], __daysOld <= 'RAG MATRIX (TESTING)'[Max Range] ) + 0Or like this:
NEW RAG = VAR __workStream = 'FACT - Backlog'[Grouping] VAR __daysOld = 'FACT - Backlog'[Days Old] VAR __result = CALCULATE ( MAX ( 'RAG MATRIX (TESTING)'[RAG] ), 'RAG MATRIX (TESTING)'[Workstream] = __workStream, __daysOld >= 'RAG MATRIX (TESTING)'[Min Range], __daysOld <= 'RAG MATRIX (TESTING)'[Max Range] ) RETURN IF ( __result = BLANK (), 0, __result )Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.