cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElliotP
Post Prodigy
Post Prodigy

Too many Nested IF functions, hoping for an alternative

Good Evening,

 

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

And:

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

9 REPLIES 9
GilbertQ
Super User
Super User

Hi @ElliotP,

 

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

Sean
Community Champion
Community Champion

@ElliotP

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

Thoughts?

Hi @ElliotP,


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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

v-ljerr-msft
Microsoft
Microsoft

Hi @ElliotP,

 

Have you tried the solution provided by @SqlJason? The formula looks good for me. Does it work in your scenario?Smiley Happy

 

Regards

SqlJason
Memorable Member
Memorable Member

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.

 

Grp

Breakfast
None
Coffee
None + Lunch + Coffee
None + Lunch
Juice and Smoothie + Breakfast + Coffee + Coffee + Lunch
Breakfast + Juice and Smoothie
Lunch
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))," + "))
, [Grp])

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

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.