Blank Values on KPI Card

[Anonymous]

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. Below are my DAX measures. To me, when values are displayed as BLANK it looks sloppy. So I tried an IF statement and I'm getting either NaN or Inifity value. Any of you DAX Masters have any suggestions on how to solve this issue? 

Thanks 

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

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

[MFelix]

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]

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]

Hi @Anonymous,

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

Regards,

MFelix

[Anonymous]

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! 

[MFelix]

Hi @Anonymous,

Not abble to check how you have the final result in this type of card, not abble to repeat your values.

Can you share some sample data?

Regards,

MFelix