At the moment I'm attempting to create the possibly order combinations from a list of item's ordered catagories such as "Coffee + Lunch Meal".
My transactions data comes in where there can be multiple rows per transaction (one payment), but they all share the same unique transaction ID.
At the moment, the best way I've found to create these Order combinations is through nested IF functions, but as soon as I expand from just doing the catagories "Food" "Drink" and "Food+Drink" it becomes quite a lot of IF functions and possible 'total' combinations such as "Coffee+Lunch M" "Alcohol+Breakfast M" type situation.
At the moment I can just do a load of IF functions (100s), but I was hoping there might be a better way. A way in where it could simply take the catagories which appear in the catagory column for that unique transaction ID and combine them together with + being the seperator.
My current bits of code are:
Count = CALCULATE(COUNTROWS('itemdetailsdogfood$'), ALLEXCEPT('itemdetailsdogfood$','itemdetailsdogfood$'[Transaction ID]))
Calculated Column = IF('itemdetailsdogfood$'[Sections1]="Custom Amount","Food + Drink",IF('itemdetailsdogfood$'[Count]=1,IF('itemdetailsdogfood$'[Sections1]="Kitchen","Food","Drinky"),"Food + Drink"))
They are both columns and something I need to be able to work out the volumes of each catagories. It uses the first column to work out if there are more than 1 row for that transaction ID and then the second column IF functions it away.
Here is my pbix:
Any help would be greatly appreciated (working on the 'itemdetailsdogfood$' table btw): https://1drv.ms/u/s!At8Q-ZbRnAj8hjIV_LCfjAmx4QJF
What about if you can create an ID column in the Query Editor?
This can be a combination of what you need to select to make it unique in terms of what is included as part of each TransactionID?
In doing so you can then use this column as a way to then identify the different types? As well as then create another table based of the unique distinct items? Which then means you can use this table as a dimension within your data. As well as you could also add in additional data on your table, which can then enrich the model?
@GilbertQthat's a good idea, but I'm not sure how we solve the issue of there being two catagories for the same items like:
Scrambled Eggs + Smoothie
Smoothie + Scrambled Eggs
When I would like the catagory to be for example "Scrambled Eggs + Smoothie"
How about this...
Test = SUMMARIZE ( SUMMARIZE ( 'itemdetailsdogfood$', [Transaction ID], "Grp", CONCATENATEX ( 'itemdetailsdogfood$', CALCULATE ( FIRSTNONBLANK ( 'itemdetailsdogfood$'[Category], 1 ) ), " + ", 'itemdetailsdogfood$'[Category], ASC ) ), [Grp] )
@GilbertQinteresting idea. hmm. My greatest concern is we hit the massive amount of nested IF functions again we're trying to avoid where we have to IF function every combination type to appear as the set type.
@SeanWhat does it do? I've noticed it sorts the columns by the alphabetical order of the second filter, do we think changing it to:
Test = SUMMARIZE ( SUMMARIZE ( 'itemdetailsdogfood$', [Transaction ID], "Grp", CONCATENATEX ( 'itemdetailsdogfood$', CALCULATE ( FIRSTNONBLANK ( 'itemdetailsdogfood$'[Items], 1 ) ), " + ", 'itemdetailsdogfood$'[Section1], ASC ) ), [Grp] )
Would sort the items by the alphabetical order of the higher granuality Section1 than Items; I don't think it solves the issue of duplicate appearances though. Hmm
What I would do is to put in both combinations so that you do not have any issues in terms of losing the data.
And then I would have an Additional column (Meal Type) which I would use for Display purposes, and in this column (Meal Type) is where I would give both the "Scrambled Eggs + Smoothie" or "Smoothie + Scrambled Eggs" a value of "Scrambled Eggs + Smoothie"
So in doing it this way, the values used in Visuals would be consistent, and you would not loose any data.
Not sure I got this completely. But does making a calculated table with the following code solve your issue?
The results I get are also pasted below.
|None + Lunch + Coffee|
|None + Lunch|
|Juice and Smoothie + Breakfast + Coffee + Coffee + Lunch|
|Breakfast + Juice and Smoothie|
|Coffee + Coffee|
If you could paste the results that you want, I might be able to help you further. But ConcatenateX function seems the key to convert your rows into one concatenated list.
Test = SUMMARIZE(
SUMMARIZE('itemdetailsdogfood$', [Transaction ID], "Grp",
CONCATENATEX('itemdetailsdogfood$',CALCULATE(FIRSTNONBLANK('itemdetailsdogfood$'[Category],1))," + "))
@SqlJasonSorry for being tardy with a response. I got wrapped up in something else. I just tried it again (when i first tried it, I made it as a column and not a table - my mistake) and it works perfectly. I cannot thank you enough. This is truly amazing and everything I could have asked for, thank you so much.
How do you think I would create a column next to it which amalgamated the groupings as so they did not appear twice just under different names.
So for example; instead of it appearing as:
Scrambled Eggs + Smoothie
Smoothie + Scrambled Eggs
On the next line, how would I be able to just show one defined type? Would it be best to use a conditional column and deal with it that way (creates the nested IF functions again issue)
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.