Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unable to replace blank values cells

Hi,

 

I am unable to replace the blank values with "NA".

Here is the situation, there are some metric names that available in data source for the sept month but the values are empty,  and for the oct month they( same metrics) are not available, meaning no entries of those.

I can replace the blank cells with NA for sept month but for oct month it just returns blank. Since the row entry is not there for oct month i am not able to replace the blank values with "NA", is that the case?

 

i have created a sample matrix to show in which months the metric name records are available in data source. Last 4 metrics in the table below for the month of october has no entries in the data source hence it is showing blank.

 

 

Table1.PNG

 

 

 

 

 

 

 

 

 

 

Table 2 : I was able to replace(measure mentioned below) the blank value cells for sept month since the metric names were available in the data source ,however for october month there were no entries at all ( meaning no row entries for same metrics in the data source ) which did not get replaced with "NA" using the measure below. Not sure whats the issue here, could someone please help how can i solve this issue.

 

Test =
var b = AVERAGE(archer_kri_latest_summary[derived_value])
return IF(ISBLANK(b),"na",b)

 

Table2.PNG

 

Regards,

DSharma

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Create a seperate table for metric_name and join back it with you table and use mertic_name from the new tbale, Then this formula should work

 

New Table

mertic = distinct(mertic_name )

ShowItemwithoutdata.JPG

Also explore this option

 

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@Anonymous , Create a seperate table for metric_name and join back it with you table and use mertic_name from the new tbale, Then this formula should work

 

New Table

mertic = distinct(mertic_name )

ShowItemwithoutdata.JPG

Also explore this option

 

Anonymous
Not applicable

Thanks for the reply amit.

do you know why this is not achievable in one single flat table?

 

Regards,

Dsharma

 

@Anonymous , This what I think, You need a left join to have that. As the values are there for sep as blank you are getting that. In same table left join in not possible with a column. Once you create a new table left join is possible; and +0 or isblank can force that join in a measure

amitchandak
Super User
Super User

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

Hi,

 

Unfortunately i am unable to attach the data source file or pbi file so attaching the screenshots of data source file and output

Snapshot 1 : datasource

Snapshot2 : Output

 

Table4.PNGtable5.PNG

Anonymous
Not applicable

Replicated in excel file

Data source file.PNG

amitchandak
Super User
Super User

@Anonymous , Try like

Test =
var b = AVERAGE(archer_kri_latest_summary[derived_value]) +0
return IF(ISBLANK(b) || b=0 ,"na",b)

 

or

 

Test =
var b = AVERAGE(archer_kri_latest_summary[derived_value]) +0
return IF(ISBLANK(b) || b=0 ,"na",b&"")

Anonymous
Not applicable

Hi Amit,

 

thanks for replying, i tried both but it did not work.

 

table3.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.