Deleting (or merge) Products from a Basket - Basket Analysis

[Anonymous]

Hello!

I’ve been trying how to avoid the “Duplicate basket” In my Power BI project. The situation is that I’ve a dataset with transaction and I want to create a Basket. So, for example, I’ve this dataset:

As you notice the basket is the same but the order is different. So, when I write the Dax Formula:

Canasta = 
CALCULATE (
    CONCATENATEX ( Ventas; Ventas[Product ID]; "-" );
    FILTER (
        ALL ( Ventas );
        Ventas[Transaction ID] = EARLIER ( Ventas[Transaction ID] )
    )
)

And i obtain this result:

And when I create a new table without the duplicate I’ve two baskets and I should have one (In theory, because the problem is that I don’t kwon how to compare the Texts) So I’ve this result:

There is a way where I can filter o make another calculation to integrate the two baskets only in one.

The Power BI project is here: https://files.fm/u/bd5q2v23

Thanks!

[Greg_Deckler]

Perhaps if you do a Sort by your Product ID column in your query, then when you concatenate the values they will be in the same order?

[Anonymous]

and how should I do that? Do you have any link where I can see a way to sort the columns how do you say it?

Thanks!

[v-yuta-msft · Accepted solution]

Hi joaco ,

To achieve your requirement, first create a ranking column based on each Transaction ID group using DAX below:

Rank =
RANKX (
 FILTER ( Ventas, Ventas[Transaction ID] = EARLIER ( Ventas[Transaction ID] ) ),
 RANKX ( ALL ( Ventas ), Ventas[Product ID] ),
 ,
 ASC,
 DENSE
)

Then create a calculate column Canasta using DAX like this and check if it can meet your requirement.

Canasta =
IF (
    CALCULATE (
        COUNTROWS ( Ventas ),
        FILTER (
            Ventas,
            Ventas[Rank] = EARLIER ( Ventas[Rank] )
                && Ventas[Product ID] <> EARLIER ( Ventas[Product ID] )
        )
    )
        = 0,
    CONCATENATEX ( VALUES ( Ventas[Product ID] ), Ventas[Product ID], "-" ),
    CALCULATE (
        CONCATENATEX ( Ventas, Ventas[Product ID], "-" ),
        FILTER (
            ALL ( Ventas ),
            Ventas[Transaction ID] = EARLIER ( Ventas[Transaction ID] )
        )
    )
)

Hope it's helpful to you.

Regards,

Jimmy Tao