Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Return N/A value

Hi,   Just wondering if anyone cn help with my query, I need the below DAX to return N/A when the current year is not selected on the filter I have on the page. Wehn I select 2020 for instance I ge...
  • d_gosbell's avatar
    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" )