cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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? 

 

CRM.PNG

 

1 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])

 





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

Proud to be a Super User!







Power BI Blog

View solution in original post

8 REPLIES 8
GilbertQ
Super User
Super User

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.





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

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

@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]))

 

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.





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

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

@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])

 

 

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])

 





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

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

@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.

 

 

CRM.PNG

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.





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

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.