If statement to check if a value is between two values and return text

[UK_User123456]

Hi All,

Probably really simple but I am trying to return text if a number falls between two values as follows:

Difference

3

100

500

320

410

210

If one of the above falls between 0 - 365 return "Active"

I tried using the following DAX statement, but it wont return a text

IF(AND([Difference] > 0 , [difference] < 365 ), "Active" )

TIA 

[JK-1]

Is there a way to nest all these so the string can continue - with || 

IF(and(Table1[Difference]>0 , Table1[Difference]<365),"Active",BLANK())

IF(and(Table1[Difference]>365 , Table1[Difference]<500),"Active2nd",BLANK())

IF(and(Table1[Difference]>500 , Table1[Difference]<726),"Active3rd",BLANK())

think from reading switch would only work if true/false Boolean and here I'd want some text returned, not sure if its the > causing additional problems on multi lines or need some form of array 

[gmsamborn]

Hi @UK_User123456 

Would a measure like this help?

Result =
	SWITCH(
		TRUE(),
		AND( 'Table1'[Difference] > 0 , 'Table1'[Difference] <= 365 ),
			"Active",
		AND( 'Table1'[Difference] > 365 , 'Table1'[Difference] <= 500 ),
			"Active2nd",
		AND( 'Table1'[Difference] > 500 , 'Table1'[Difference] <= 726 ),
			"Active3rd",
		BLANK()
	)

Let me know if you have any questions.

[JK-1]

Thank you so much, very helpful!

{in separate query} again using the IF / OR where there is only going to be 2 choices would the below work as an IF?

Result = IF(OR(Table1[Name]="Toni", Table1[Name]="Terry"),"12","7") these obviously would return 12 for both Toni and Terry in a true match, and 7 for any others, as false

but how could I write this to be: if Toni assign 12, OR if Terry assign 7 .. would it need to be split across an IN {list} query / containsstring? Just having difficulty assigning between the 2

[gmsamborn]

Hi @JK-1 

The following 2 measures return identical results.  Additional names can be added between the braces{}.  

(I added a 3rd level for demonstration purposes.)

Result =
VAR _Name = MAX( 'Table1'[Name] )
RETURN
	IF(
		_Name IN { "Toni" },
		12,
		IF(
			_Name IN { "Terry" },
			7,
			IF(
				_Name IN { "Tommy" },
				6,
				0
			)
		)
	)

Result =
VAR _Name = MAX( 'Table1'[Name] )
RETURN
	SWITCH(
		TRUE(),
		_Name IN { "Toni" },
			12,
		_Name IN { "Terry" },
			7,
		_Name IN { "Tommy" },
			6,
		0
	)

I hope this helps.

[JK-1]

Thanks for both examples. Not been on for a few days but these are great. Cheers

[gmsamborn]

Hi @JK-1 

I'm glad they helped.

[Anonymous · Accepted solution]

Result = IF(and(Table1[Difference]>0 , Table1[Difference]<365),"Active",BLANK())

[sakarim]

This works great! I am wondering if it is possible to show the text as a label on a bar chart. It appears when you select "show as a table" but not on the actual bar chart. Any thoughts?