cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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

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:Excel.JPG

 

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:Dos Canasta.JPG

 

 

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:Diferente.JPG

 

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

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

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

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

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

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?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Anonymous
Not applicable

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

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!

Community Support
Community Support

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

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

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors