Too many Nested IF functions, hoping for an alternative

[ElliotP]

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

[GilbertQ]

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?

[ElliotP]

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

@ElliotP

How about this...

Test =
SUMMARIZE (
    SUMMARIZE (
        'itemdetailsdogfood$',
        [Transaction ID],
        "Grp", CONCATENATEX (
            'itemdetailsdogfood$',
            CALCULATE ( FIRSTNONBLANK ( 'itemdetailsdogfood$'[Category], 1 ) ),
            " + ",
            'itemdetailsdogfood$'[Category], ASC
        )
    ),
    [Grp]
)

[ElliotP]

@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

[ElliotP]

Thoughts?

[GilbertQ]

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.

[v-ljerr-msft]

Hi @ElliotP,

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

Regards

[SqlJason]

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

[ElliotP]

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