Forum Discussion

S_JB's avatar
S_JB
Icon for Resolver III rankResolver III
5 years ago
Solved

How to use the same value twice in an IF statement

Hi all,

 

In my dataset there is a field called "Availability" with values of 0 and 1. I want to create a new column which labels records with the value of 1 "available" then any instances with the values of either 0 and 1 "All".

I tried an IF statement to see if it would allow the usage of the same value twice (please see the logic below):

Availability =
IF(
'Table 1'[Availability ] IN { 1,0 },
"All",
IF(
'Table 1'[Availability] = 0,
"Unavailable"
))

 

My understanding of why this doesn't work is because the IF statement looks at the first line of the logic and will assign all records with a value of 0 and 1 to "All", so the second line will have no records left to allocate. If I reversed the order this would work in a similar manner and remove all 0 instances, only allocating instances with the value of 1 to "All".

 

Is there a way that I can tweak the IF statement to all me to use the same value twice? Or do I need to take a different approach?

 

Hopefully the above makes sense and you understand what I am trying to achieve!

Need Help
  • S_JB's avatar
    S_JB
    5 years ago

    Thanks for this suggestion amitchandak 

     

    Unfortunately this did not work and labelled all instances the same.

     

    However, I managed to get around it with a simple solution. I created a column using the below logic:

     

    Availability = IF('Table 1'[Availability] = 1,"Available","N/A")

     

    I then put a filter on this slicer to remove "N/A" instances. The slicer has been defaulted to "Available" but if "Select all" is selected then this will show both "Available" and "Unavailable" instances.

     

    Thanks again.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    S_JB , I think you have to try a measure like

    IF( hasonevalue('Table 1'[Availability]) , if( selectedValue('Table 1'[Availability]) = 0,"Unavailable", "Avaiable"),"All")

     

     

    • S_JB's avatar
      S_JB
      Icon for Resolver III rankResolver III
      5 years ago

      Thanks for this suggestion amitchandak 

       

      Unfortunately this did not work and labelled all instances the same.

       

      However, I managed to get around it with a simple solution. I created a column using the below logic:

       

      Availability = IF('Table 1'[Availability] = 1,"Available","N/A")

       

      I then put a filter on this slicer to remove "N/A" instances. The slicer has been defaulted to "Available" but if "Select all" is selected then this will show both "Available" and "Unavailable" instances.

       

      Thanks again.