Change value for "New Measures" and "New Columns"

[Anonymous]

I've got a analysis dashboard to show the campaign response from contacts.  Below is the table for Recency, which is a calculated "New Column",  the table works perfectly fine.  But, with the "new customer" coming (because in the database, they are new, the "Recency" calculation value is blank), the "Response%" Measure is "Infinity" which looks ood.   How to:

1. if "Response%" Measure is "infinity", leave it blank
2. if they are new cutomer which Recency calculation column is blank, make "Recency" = "New Customer"  (i can't go "edit Queries" to change the blank value for "Recency", because "Recencey" is not exist in the data source, it's a created "new column" 

is it possible? 

[GilbertQ]

Hi @Anonymous

What you can do for the Reponse% measure is the following below

Response % New = IF(ISBLANK([7VResp%]),BLANK,[7VResp%)

That will look at your measure and evaluate it, if it is BLANK the make the data return BLANK, else return the measure value.

For your New Column, I would put that logic into your Created Column similar to above. I personally do not create any columns in the Power BI Model,but rather do it in the Query Editor as I find it a lot easier with all the Query Editor functionality that makes it really easy to do.

[Anonymous]

@GilbertQThe "new measures" works fine like below. 

7AResp% = if(ISBLANK(([Count7AGift]/[Count7AList])),BLANK(),([Count7AGift]/[Count7AList]))

but the "new column" not work, the RecencyTest blank still blank, i would like it to show up as "New". 

RecencyTest = if(ISBLANK('[Recency:LastGift]),"New",'[Recency:LastGift]))

[GilbertQ]

Hi @Anonymous for the New Column try this

RecencyTest = IF('TableName'[Column Name] = "","Blank Value",'TableName'[Column Name]) 

The thing to note above is that I am NOT use any calculated measures, which it appears as what you are doing below, it has to be the table and column name.

Also make sure where you create your new column, you select the table where the columns you are looking for exists.

[Anonymous]

@GilbertQ i do have table names, i've removed it last time when i copied here. i've tried below, but non of those worked.

RecencyTest = if('table'[Recency:LastGift]="","New",'table'[Recency:LastGift])

RecencyTest = if(ISBLANK('table'[Recency:LastGift]),"New",'table'[Recency:LastGift])

[GilbertQ · Accepted solution]

Hi @Anonymous, the first one is correct.

Now the challenge is how your data is actually stored when it is blank.

Reason is if you go into the Query Editor and look at the column, does it have a value of "null", or is it just blank (in that there is no data shown).

If it is a "null", then your current new column syntax should work. If it is indeed blank try and put a space between the double quotes

RecencyTest = if('table'[Recency:LastGift]=" ","New",'table'[Recency:LastGift])

[Anonymous]

@GilbertQ  the tricky thing is everyone on the "Contacts" table will have "Recency", there is no "blank" values.  this table holds the contact list for the communication.  "DA" table holds the responses back, some are from "Contacts" table, some are totally new. For example, email sent out to person A, B, C, D, and response back from B, D, E.   in this case, E don't have "recenty", E's response% is "infinity" as we didn't send out him the communication.   hope it make sense. 🙂  thanks.

[GilbertQ]

Hi @Anonymous

What you are saying does make sense from an understanding point, but due to not understanding the actual data and process and what it all looks like I am unsure as to how to get it to work.

It might be possible to create a calculated table with the requirements.

[Anonymous]

@GilbertQ thanks so much, i will close it for now.