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
UK_User123456
Resolver I
Resolver I

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
evandrocunico
Resolver III
Resolver III

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
evandrocunico
Resolver III
Resolver III

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.

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

jdbuchanan71
Super User
Super User

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

@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

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

dateswitch.jpg

@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

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

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! 


Connect on LinkedIn

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.