Forum Discussion

ElliotP's avatar
ElliotP
Icon for Post Prodigy rankPost Prodigy
9 years ago
Solved

If columnA and Column B equal then this

 

 Afternoon guys,

 

I was hoping to create a visual which will display for example "Food + Drink". So when someone orders fritters and a flat white, it shows up in my Food and Drink Column and from that I can create a column which says "Food + Drink".

 

I've thought of going into this a few ways (Concantrate but its limited to two text strings), An IF function, but I can't see to get it to "If Food = Kitchen and Drinks = Drinks" write Food + Drink.

 

A custom column might be the best option but I'm not sure about the code (Useful link: http://community.powerbi.com/t5/Desktop/If-text-column-CONTAINS-specified-value-give-me-what-I-want/m-p/99455)

 

Thoughts?

 

I'd like to be to do it so each of them had a catagory. So if someone just ordered food, it would just say food. I'm going to make it more granular later (like Coffee + Breakfast), but it's the same principles.

 

Edit: Maybe merging queries somehow? http://community.powerbi.com/t5/Desktop/HOW-Calculated-Column-if-quot-this-column-quot-matches-quot-this/td-p/26605/page/2

 

Edit2: Custom Colum with:

if ([Food] = Kitchen or [Drink] = Drink) then "Food + Drink"

 ^Doesn't like the code and I'm not sure why; might be missing a if else component?

 

Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    To cater for the various combinations, how about something like:

    Food + Drink = IF ( ISBLANK ( Table1[Food] ) ,
					IF ( ISBLANK ( Table1[Drink] ),
							BLANK (),
							"Drink" ), 
					IF ( ISBLANK ( Table1[Drink] ) ,
							"Food",
							"Food + Drink" )
		)

22 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    To cater for the various combinations, how about something like:

    Food + Drink = IF ( ISBLANK ( Table1[Food] ) ,
					IF ( ISBLANK ( Table1[Drink] ),
							BLANK (),
							"Drink" ), 
					IF ( ISBLANK ( Table1[Drink] ) ,
							"Food",
							"Food + Drink" )
		)
    • ElliotP's avatar
      ElliotP
      Icon for Post Prodigy rankPost Prodigy
      9 years ago

      As a custom column?

       

      I tried that and it didn't seem to like to it (even after I removed the food+drink = component.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        That is as a column as you were trying to achieve, not a measure.  It may be that the columns are empty rather than null.  If you're getting "Food + Drink" for everything, try this:

        Food + Drink = IF( Table1[Food] = "" ,
			IF ( Table1[Drink] = "" ,
				BLANK (),
				"Drink" ), 
			IF ( Table1[Drink] = "" ,
				"Food",
				"Food + Drink" ))