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
Rajkumar07
Helper I
Helper I

Want to replace 0 wherever there is no data while creating a calculated column

Hi Team,

Test Users who view or edit =
  var x = IF('FILE_ACTIVITY_F'[ID_Tool] = 2 &&
'FILE_ACTIVITY_F'[N_ViewedOrEditedFileCount] >0,1,0)
return
IF(ISBLANK(x),0,x)

I've tried using the above formula but i'm unable to get the exact result.

Rajkumar07_0-1663655312994.png


Let me know if there any other alternateway to achive 0 for blank or no data.




10 REPLIES 10
v-zhangti
Community Support
Community Support

Hi, @Rajkumar07 

 

I have no problem testing with your formula.

vzhangti_0-1663751525771.png

What are the difficulties you are experiencing?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Team,

Thank  for your quick response,

I would like you inform you this, this how i'm having the data set and the sample data is given below

Rajkumar07_0-1663753148610.png


Wherever there is no data for the period I want to replace with 0.

Is that possible to get the result. Please advice.

Hi, @Rajkumar07 

 

Measure:

Test Users who view or edit = 
var x = IF(SELECTEDVALUE(FILE_ACTIVITY_F[Id no])=2 &&
SELECTEDVALUE(FILE_ACTIVITY_F[Users who view or edit])>0,1,0)
return
IF(ISBLANK(x),0,x)

vzhangti_0-1663754173841.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi Team,

Thank you for your quick response,

Yeah, I'm expecting the same way of result. But, when I tried to implement the same method i'm unable to get the result

Here is my result.

Rajkumar07_0-1663755202751.png

 

Please advice.


Hi Team,

 

Adding formula for your ref,

Test Users who view or edit =

var x = IF(SELECTEDVALUE('FILE_ACTIVITY_F'[ID_Tool] )= 2 && SELECTEDVALUE( 'FILE_ACTIVITY_F'[N_ViewedOrEditedFileCount]) >0,1,0)

return

IF(ISBLANK(x),0,x)

Hi Team,

 

Could you please confirm the above tested function its a measure or calculated column?

When i'm trying to created a calculated column with the above formula I'm unable to get the expected result.
The reason "IF" condition it's checked everything in the firstline it self. Then it will not come and check in the return function. This is my understanding.


Hi, @Rajkumar07 

 

My test is a calculated column. Can you provide sample data for [ID_Tool] and [N_ViewedOrEditedFileCount]?

 

Best Regards

MahyarTF
Memorable Member
Memorable Member

Hi,

What is the datatype for [ID_Tool] and [N_ViewedOrEditedFileCount] and also are the column or measure ?

would you please share the sample data for the empty line here ?

 

Mahyartf

Hi,

 

[ID_Tool] and [N_ViewedOrEditedFileCount] both columns are whole number.

I've created a calculated column with the help of if condition to achive the kpi condition.
Is there a way to replace 0 if there is no data?

 

Hi @Rajkumar07 ,

As the columns are the number type it should work.

Would you please create the table visual that is included the [ID_Tool], [N_ViewedOrEditedFileCount], and [Test Users who view or edit] fields to clarify the records with null value for the particular column

Mahyartf

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.