Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Blank Values on KPI Card

Good morning,    I'm have an issue with DAX in two measures I'm attempting to show on Card w/States. Basically I'm trying to show the number of instagram fans in the current month and prior month. ...
Need Help
MFelix's avatar
MFelix
Super User
8 years ago

Hi Anonymous

 

Just add a + 0 to your measures, should return 0 when value is blank so your formulas will look:

 

CurrentPageFans =
CALCULATE (
    SUM ( 'FBInsightsData'[page_fans] ),
    FILTER (
        'FBInsightsData',
        'FBInsightsData'[MetricDate] = MAX ( 'DateSeries'[menddate] )
    )
) + 0



PriorPageFans =
CALCULATE (
    SUM ( 'FBInsightsData'[page_fans] ),
    FILTER (
        'FBInsightsData',
        'FBInsightsData'[MetricDate] = MIN ( 'DateSeries'[sdate] )
    )
) + 0

Regards,

MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi MFelix

     

    That worked for the CurrentPageFans measure perfectly. Thank you for that! 

     

    However where I'm calculating the prior period it's showing the following. Any thoughts? 

     

     

     

    • MFelix's avatar
      MFelix
      Super User
      8 years ago

      Hi Anonymous,

       

      The NaN% from where? How are you setting up your information what is the visual you are using.

       

      Regards,

      MFelix

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hello,

         

        Sorry for the late response, I'm not sure how I missed it. Thank you for replying and helping! I have a been instructed to show on a KPI visual of sorts the Sum of Fans based on Month Year date. When there is no value I need it to show "na", which I'm able to do just fine. However, I'm using the Card with States by OKViz. I'm showing the variance as percent over target. So obviously when there is a value of na it's trying to convert that to a %, and ends up being NaN%. I'm trying to do some DAX trickery and whenever there is no value for the prior month make it just show "na" and haven't been able to get anywhere.

         

        So, in my screenshot I have the layout of the data, and my settings for the visual as well as a filter on a month where there is no value for the prior month which then shows the issue on the visual. Hope this makes sense. 

         

        Thanks in advance!