Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
Regards,
DSharma
Solved! Go to Solution.
@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 )
Also explore this option
@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 )
Also explore this option
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
@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
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
Replicated in excel file
@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&"")
Hi Amit,
thanks for replying, i tried both but it did not work.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |