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 get Blank in my card visual 

 

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

 

RETURN

CALCUALTE([Total Tickets],

DATEADD(ContextTable, -1, MONTH))

 

Many thanks in advance

      

 

Need Help
Show and Tell
  • d_gosbell's avatar
    d_gosbell
    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" )

     

2 Replies

  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    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" )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi d_gosbell 

       

      Thank you so much both solutions work perfectly, plus I learnt the COALESCE function which is even better so thanks again.