Forum Discussion

UK_User123456's avatar
UK_User123456
Icon for Resolver I rankResolver I
7 years ago
Solved

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

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 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago
    Result = IF(and(Table1[Difference]>0 , Table1[Difference]<365),"Active",BLANK())

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    Result = IF(and(Table1[Difference]>0 , Table1[Difference]<365),"Active",BLANK())
    • sakarim's avatar
      sakarim
      Frequent Visitor
      3 years ago

      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?

       

  • JK-1's avatar
    JK-1
    Icon for Helper II rankHelper II
    1 year ago

    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's avatar
      gmsamborn
      Icon for Super User rankSuper User
      1 year ago

      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's avatar
        JK-1
        Icon for Helper II rankHelper II
        1 year ago

        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