cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Returning text based on difference

Hi All,

 

Is there a way of returning text based on a number that I have got e.g. the difference between date(s)?

 

e.g. 

Date 1Date 2Date DifferenceText
01/01/201901/06/2019151Exisiting
12/05/201901/07/201950New
01/06/201831/10/2018152Old

 

I have tried nested if statements but all I get is that for every customer we have it displays the same text for every date we have, I just want it to return the text based on the records I need. 

 

TIA

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Returning text based on difference

Hi @UK_User123456 

 

create a column:

 

date diference = DATEDIFF(date1;date2;DAY)  (use semicolon or comma)
 
create another column:
text result = If (date diference <= 50;"New";date diference <= 150;"Existing";"Old")
 
best regards.

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Returning text based on difference

Based on your example and assuming [Date Difference] is a measure this should work.

Text =
SWITCH(
    TRUE(),
    [Date Difference] = 151, "Existing",
    [Date Difference] = 50, "New",
    [Date Difference] = 152, "Old",
    "Other"
)

Re: Returning text based on difference

@jdbuchanan71  Thanks for this, but it then throws out the error of MdxScript(Model) (140,98), 'SWITCH' does not support comparing values of type True/False with values of type text. I dont have any columns or values that are of Ture/False types, so not sure why it wont accept the measure?

 

TIA

Super User
Super User

Re: Returning text based on difference

@UK_User123456 , You will need to share your model, the measure works in my test:

dateswitch.jpg

Re: Returning text based on difference

@jdbuchanan71see below the screenshots. The data types should are the same, as there are no true/false. 

I wanted to be able to say that if the "Date Difference is between a certain number it would return the text I wanted it to display. I have also attached the error message that I receive.

 

Power BI error.JPG

Power BI example 3.JPGPower BI example.JPG

tex628 New Contributor
New Contributor

Re: Returning text based on difference

Can you share the nested IF statement that you tried? I dont see any reason as to why a nested if statement wouldnt work here! 

Super User
Super User

Re: Returning text based on difference

Is your field 'Date Difference Last Gift (By Day)' a measure of a column from a table?

 

If you want to change the compare you can change to operator in the measure.

Text =
SWITCH(
    TRUE(),
    [Date Difference] < 50, "New",
    [Date Difference] < 151, "Existing",
    [Date Difference] >= 152, "Old",
    "Other"
)

Re: Returning text based on difference

Hi @UK_User123456 

 

create a column:

 

date diference = DATEDIFF(date1;date2;DAY)  (use semicolon or comma)
 
create another column:
text result = If (date diference <= 50;"New";date diference <= 150;"Existing";"Old")
 
best regards.

View solution in original post

Re: Returning text based on difference

@evandrocunico many thanks, will try this out. I have created a new column already for date diff and this throws out some random numbers, but I think I know where the issue lies.

 

I will respond once I have had the chance to try it out.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 253 members 2,617 guests
Please welcome our newest community members: