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
ElliotP
Post Prodigy
Post Prodigy

If columnA and Column B equal then this

 

3b5aae65ec75419ee6a8cbd9ba9dd4eb.png

 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/...)

 

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

 

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?

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

22 REPLIES 22
Anonymous
Not applicable

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

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
Not applicable

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

Your code worked as column and I feel like I have an ok enough idea to be able to manipulate it to go more granular. If you wouldn't mind explaining more how it specifically works that would be much appreciated; other then that solved.

 

I used Countax (https://msdn.microsoft.com/en-us/library/ee634219.aspx) to count them.

 

Thanks so much

Anonymous
Not applicable

@ElliotP,

It's just testing the various combinations of your two columns:
IF [Food] is empty AND [Drink] is empty then set [Food + Drink] to a blank

IF [Food] is empty AND [Drink] is NOT empty then set [Food + Drink] to "Drink"

IF [Food] is NOT empty AND [Drink] is empty then set [Food + Drink] to "Food"

IF [Food] is NOT empty AND [Drink] is NOT empty then set [Food + Drink] to "Food + Drink"

 

Depending on your original dataset and what you are trying to present in your visuals there are likely better ways to model the data and create the columns and measures you want to present.

How would I then be able to rank them by the value of the count?

 

I'm using

Food+DrinkMeasure = COUNTAX(FILTER('OrderCombination', [Food + Drink]="Food + Drink"), [Food + Drink])

for each area, but I'm unable to sort from highest to lowest.

 

So I would have a list "Food+Drink" 3, "Drink" 2.

 

 

153e98a430317e09be49f8bf83810ea1.png

 

 

b1266b8437f103b04d90cb3af0742f9f.png

 

 

 

Anonymous
Not applicable

@ElliotP,

I'm not sure what your criteria are for "So I would have a list "Food+Drink" 3, "Drink" 2."  What about Food only - is that a 1?

 

Can you just sum up your order item counts - e.g. 

Order Weight = Table1[FlatWhite] + Table1[ScrambledEggs] + Table1[Fritters] + Table1[Smoothie]

This will likely create work as you add items to the menu = you'll need to create now count columns and remember to update the measure.

 

Depending on your source data structure, a more dlexible appraoch may be as follows:

1. Unpivot the data via Edit Queries to get the Items (e.g. Smoothie, Fritters) in rows not columns (and remove the Counts of 0):
FoodDrinkTxns.PNG

 

2. Add an [Item Category] for Food or Drink based on the item.  You can do this as a Conditional Column while in Edit Queries, or as a Column via DAX - e.g. 

Item Category =
SWITCH (
    Table2[Item],
    "FlatWhite", "Drink",
    "Smoothie", "Drink",
    "ScrambledEggs", "Food",
    "Fritters", "Food",
    "Unknown"
)

3. Add helper measures to count [Food Orders] and [Drink Orders]:

Food Orders = 
CALCULATE (
    SUM ( Table2[Count] ),
    FILTER ( Table2, Table2[Item Category] = "Food" )
)
Drink Orders = 
CALCULATE (
    SUM ( Table2[Count] ),
    FILTER ( Table2, Table2[Item Category] = "Drink" )
)

4. The [Food + Drink] column then becomes a measure, with basically the same logic:

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

5. Then you can add measures for "Food Only Orders" , "Drink Only Orders", "Food + Drink Orders", changing the red FILTER criteria as needed - e.g.  (EDIT: need to summarize in FILTER by Transaction ID first to get count by order, not item...)

Food Only Orders =
CALCULATE (
    DISTINCTCOUNT ( Table2[Transaction ID] ),
    FILTER ( SUMMARIZE(Table2, Table2[Transaction ID]), [Food + Drink] = "Food" )
)

I'm not sure where your dates are coming from to calculate "Past Two Weeks" or where your revenue numbers are coming from for the other measure title shown in your screenshot, but the ideas above may be enough to get you started.

 

Thank you so much for the detailed long response. Sorry of the delay, I had to focus on something else quickly.

 

After creating the measures I'm stuck at the last measure in terms of bringing it to life:

Food Only Orders = 
CALCULATE (
    DISTINCTCOUNT ( 'itemdetailsdogfood$'[Transaction ID] ),
    FILTER ( SUMMARIZE('itemdetailsdogfood$', 'itemdetailsdogfood$'[Transaction ID]), [Food + Drink] = "Food" )
)

I've been able to create the 'Food' or 'Drink' conditonal column. The table is in its original format where there are Unique transaction IDs and a number of items per. For example; A unique transaction ID is the transaction ID for that one order. So if someone orders a Flat White and Scrambled Eggs, both of them have the same transaction ID.

 

I'm stuck at being able to DISTINCTCOUNT by the transaction ID as the present when i try to create a 'Food Only' or 'Drink Only' measure and plot, they both return the same value as well as I'm not sure how to create the Food+Drink Catagory.

 

I feel I've gotten a little lost on the way through the measures, wherein I'm not sure how to see the Food+Drink Catagory, etc; I'm not sure how to bring it all to life.

 

EDIT:

 

https://gyazo.com/3119a6910be8050277d7da38a8f88899

 

I've sorted so I'm able to plot [Food Only] and [Drink only]; but I'm not sure how to plot the Food+Drink Catagory?

Anonymous
Not applicable

@ElliotP,

If you have the [Food + Drink] measure per step 4 above, you should only need a measure such as:

 

Food + Drink Orders =
CALCULATE (
    DISTINCTCOUNT ( 'itemdetailsdogfood$'[Transaction ID] ),
    FILTER (
        SUMMARIZE ( 'itemdetailsdogfood$', 'itemdetailsdogfood$'[Transaction ID] ),
        [Food + Drink] = "Food + Drink"
    )
)

 

This works amazingly.

 

 

How would I translate the Food + Drink measure into a column as so I could work with it that what? A calculated column using the same code?

Anonymous
Not applicable

We covered off 'Food + Drink' as a column earlier in this post, back on 19 Jan...

Cheers, I'm sorry. I'm swimming in dax and pbix's right now. I'll try that and see how it goes (thanks so much for all of the responses)

Morning,

 

What I've found that I would like to do is to be able to create a column based upon this data (same data as before, just in it's original data structure).

 

https://gyazo.com/652da3a1f1decce9e52aca68deaf2dba

https://gyazo.com/29f5d390dd433631e4676fc86ac48611

 

Each Line is a different product transaction where there are often multiple rows per transaction which all share the same Unique transaction ID.

 

I have the conditional columns "Sections1", "Sections2", "Sections3", "Sections2a" which show what I've catagorised each item as. I'd like to be able to give it a catagorisation such as "Food Only" "Drink Only" or "Food + Drink" depending upon the unqiue transaction IDs as well as ideally for the past two week's (by week numbers).

 

I feel for a column on the table it will need to be something like:

Food + Drink = CALCULATE(
IF([Food Category]= "Food", "Food", ), 
IF([Drink Category]= "Drink", "Drink", ),
IF([Food + Drink Category]= "Food" & "Drink", "Food + Drink", ), FILTER(ALL('itemdetails$dogfood', [transactionID])

Where we take your feels from using measures before to do the categorisation. Using the measures and the steps previously it calculated the sum of that kind as opposed to just the name to work from This doesn't allow you to then visually filter it nicely as it comes up in a funnel as not a very nice Food Only = 7; but when put into a bar chart you are unable to use for example the Section 3 as a legend.

 

So we would need to create measures for the above Column which catagorised by each transaction ID, for the past week (I can work this out after tbh), if it had Food, if it had Drink and then the column then works over those to then determine for each transaction ID what overall category it fits into "Food Only" "Drink Only" "Food + Drink" before displaying that value in the column.

Anonymous
Not applicable

@ElliotP,

I don't quite understand what you are trying to show - wthout seeing the whole picture, it's hard to suggest the best ocerall solution.  I think you can show the Order "Type" using the measures you've created - e.g. see below:

FoodDrinkOrders.PNG

 

If you want to summarise Order$ by [Food + Drink] you would admittedly need to duplicate the OrderCount measures we created, for Food Only and Drink Only and Food + Drink.

 

If you must have [Food + Drink] as a column, I think you need to create an Orders table, as [Food + Drink] is an attribute of the total order (Transaction?) not of each item - e.g.

 

Orders =
SUMMARIZE ( Table2, Table2[Transaction ID], "Food + Drink", [Food + Drink] )

Create a relationship from Orders back to Table2 and you should be able to filter etc. and also use simpler measures for order count, order $ etc, if needed.

Thanks for the response again , I really appreciate it. I would like to be be able to show what kind of order each unique transaction is.

 

As so I could plot a funnel for example which is just a count of each kind (food only, drink only, Food + drink) and be able to see it in an ascending order. At the moment, when I plot the 3 measures, I'm unable to sort them so they all as they are three different measures.

 

I had a look back at our previous measures/created columns and I'm not sure how to do the IF function for each unique transaction ID. So for Example it checks the catagory of each row of the transaction ID (might be just food; or have a food category and a drink catagory) and then after evaluating that, comes up with a catagory for that transaction ID "Food + Drink" for example, in a column as so I can do the above sorting.

Anonymous
Not applicable

@ElliotP,

Sorry, I can't quite see what you want. What needs to change about the sample graph I showed?

@Anonymous

I need to be able to manipulate it as so I can rank it by the number of occurences.

 

As so for example I could put it on a funnel for example and rank by the Sum of the count of the number of times "Food only", "Drink only" or "Food+Drink" appear as opposed to right now where they are 3 seperate measures with no common thing between them to be able to do this.

Anonymous
Not applicable

@ElliotP,

You can use the [Food +Drink] column from the summarised Order table above as an axis or group, then use simple counts or sums of your orders or items.

Morning, thanks for the response.

 

I've tried adding both of our measures below (summarize one is here) and niether seem to be accepted in the groups/legend/detail field. I've tried it with a funnel, a pie chart.

 

Food + Drink = IF ( ISBLANK ( [Food Orders] ) ,
			IF ( ISBLANK ( [Drink Orders] ),
					BLANK (),
					"Drink" ), 
			IF ( ISBLANK ( [Drink Orders] ) ,
					"Food",
					"Food + Drink" )
                )
Food + Drink Only Orders = 
CALCULATE (
    DISTINCTCOUNT ( 'itemdetailsdogfood$'[Transaction ID] ),
    FILTER ( SUMMARIZE('itemdetailsdogfood$', 'itemdetailsdogfood$'[Transaction ID]), [Food + Drink] = "Food + Drink" )
)

I agree that we are very very close, It's just trying to work out how to use:

 

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

To disentangle this:

Food Orders = 
CALCULATE (
    SUM('itemdetailsdogfood$'[Qty]),
    FILTER ( 'itemdetailsdogfood$', 'itemdetailsdogfood$'[Section2a] = "Food" )
)

So it creates the catagorisation.

I feel we have the right path. We just need the polish.

 

It might make more sense for all of the Only measure (Food Only, Drink Only, Food + Drink Only) to all use DISTINCTCOUNT of transactionID instead of sum of it 'itemdetailsdogfood'$[qty].

 

But I'm not sure how to put the [Food + Drink] measure on a visual as it will only allow it as a tooltip field. This measure is what catagorises each item; but then we need to find a common way to unite this measure which catagorises around the measure so we can COUNT the number of 'Food', 'Drink' and 'Food+Drink'.

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.