Forum Discussion
Return N/A value
- 4 years ago
This is a bit annoying, I wish the card had a setting for alternate text to return when the result is blank. But you can do this in a measure by explicitly checking for the blank value
eg
Prev Months Tickets = VAR CurrentMonth = MONTH(TODAY())
VAR currntYear = YEAR(TODAY())
VAR LastDay = EOMONTH(TODAY()),0)
VAR Firstday = DATE(CurrentYear, CurrentMonth,1)
VAR ContextTable =
CALCULATETABLE(
VALUES(Dates[Date]),
Filter(Dates,
Dates[Date] >= Firstday &&
Dates[Date] <= LastDay))
var result =
CALCUALTE([Total Tickets],
DATEADD(ContextTable, -1, MONTH))
RETURN IF( ISBLANK( result ), "N/A", result)
You could also look at using the COALESCE function for the last line
RETURN COALESCE( result , "N/A" )
This is a bit annoying, I wish the card had a setting for alternate text to return when the result is blank. But you can do this in a measure by explicitly checking for the blank value
eg
Prev Months Tickets = VAR CurrentMonth = MONTH(TODAY())
VAR currntYear = YEAR(TODAY())
VAR LastDay = EOMONTH(TODAY()),0)
VAR Firstday = DATE(CurrentYear, CurrentMonth,1)
VAR ContextTable =
CALCULATETABLE(
VALUES(Dates[Date]),
Filter(Dates,
Dates[Date] >= Firstday &&
Dates[Date] <= LastDay))
var result =
CALCUALTE([Total Tickets],
DATEADD(ContextTable, -1, MONTH))
RETURN IF( ISBLANK( result ), "N/A", result)
You could also look at using the COALESCE function for the last line
RETURN COALESCE( result , "N/A" )
Hi d_gosbell
Thank you so much both solutions work perfectly, plus I learnt the COALESCE function which is even better so thanks again.