cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jainravi Member
Member

When having no data for that month visual need to show #NA values

I am having card visual for some measure values which are  changing based on month slicer.

 

i am new in DAX

e.g Averageof%ArchivedforBaselineCircle = AVERAGE('Stage FI_CRS_DashboardInput'[PercentageArchived]) i calculated this average value and it is changing based on month selecting user from slicer.

 

Values are coming properly in visual when having data for that KPI no in database for that measure but how to handled if we don't have entry for any month in database and user selecte that month in slicer visuals are populating with #NA values . and i need to apply this all visual which are changing after selecting slicer.

 

what DAX i need to handle please help me here.

 

 

5 REPLIES 5
Super User
Super User

Re: When having no data for that month visual need to show #NA values

So, this is a bit tricky because a measure cannot return two different data types (number and text in this case). However, you can get around this with a CONCATENATE. Here is an example:

 

Measure 9 = IF(ISBLANK(COUNT(bar[b])),"NA",CONCATENATE(COUNT(bar[b]),""))

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


jainravi Member
Member

Re: When having no data for that month visual need to show #NA values

HI Thanks @Greg_Deckler 

 

when i used measure as you suggested it is working for.

 

Previously i used measure for Trend is = 

 

Trend KPI No 13 = [Baseline Month KPI NO 13]-[COM Baseline Month for KPI NO 13]

 

As per your suggestion for #NA i modified that as below and now using below 

 

Trend KPI No 13 = IF(ISBLANK([Baseline Month KPI NO 13]-[COM Baseline Month for KPI NO 13]),
"#NA",CONCATENATE([Baseline Month KPI NO 13]-[COM Baseline Month for KPI NO 13],""))

 

it is working for me when there is not data for any month it is showing #NA value, but previosuly the same measure when i applied with table visual conditional formatting option is coming but after modifying and using with #NA above one conditional formating option is not coming. please suggested me how to on that conditional formatting with 2nd one as well which i am using with "#NA". if that option come my problem will resolve so please guide me on that.

Moderator v-yuezhe-msft
Moderator

Re: When having no data for that month visual need to show #NA values

@jainravi,

Could you please share the sample data of your table so that we can test? We also need to know which fields you use to create the table visual.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jainravi Member
Member

Re: When having no data for that month visual need to show #NA values

@v-yuezhe-msft 

 

i using this filed in table visual only and try to conditional formatting on that but that option is not coming.

 

Trend KPI NO 13  this field below is formula for that.

 

Trend KPI No 13 = IF(ISBLANK([Baseline Month KPI NO 13]-[COM Baseline Month for KPI NO 13]),
"#NA",CONCATENATE([Baseline Month KPI NO 13]-[COM Baseline Month for KPI NO 13],""))

when i draged that field in table visual condition formatting is not coming.

 

But when i draged below field with this below formula, conditional formating option is coming for me.

 

Trend KPI No 13 = [Baseline Month KPI NO 13]-[COM Baseline Month for KPI NO 13]

but i want conditional formatting option on above field.  due to confidential part not able to share data or table sorry for that but how to achive that on that conditional formatting on above field. 

 

 

 

 

 

 

Moderator v-yuezhe-msft
Moderator

Re: When having no data for that month visual need to show #NA values

@jainravi,

Please share sample data of your original table, I will create the above fields in the table and test this scenario.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.