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

Modified 'Basket Analysis' - don't count when Item = Filter Item

Hi,

 

I have a list containing Order Number and Materials, where I need to figure out how many orders have the same pair of materials.

 

I've solved this using a Basket Analysis which works, where I put the measure in a table, with Materials_Primary and Materials_Filter.

However, I need to correct the measure, such that I don't get a count when Materials_Primary = Materials_Filter.

I.e. I don't want a count for 2002489 / 2002489, but I do want a count for 3024240 / 2002489.

 

How do I correct the measure to achieve this?

 

Used measure:

Orders with both products = 
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[Order number] );
    CALCULATETABLE (
        SUMMARIZE ( 'Sales'; Sales[Order number] );
        ALL ( 'Sales' );
        USERELATIONSHIP ( 'Sales'[Materials]; Materiale_Filter[Materials] )
    )
)

Output example:

Tabel.PNG

Used Model:

Model.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Community Champion
Community Champion

Re: Modified 'Basket Analysis' - don't count when Item = Filter Item

Hi @Anonymous

 

Add EXCEPT ( Materiale_Filter; Material ) to CALCULATETABLE - see below.

 

By the way, I would have expected ALL ( Materiale ) rather than ALL ( 'Sales' ) in the standard Basket Analysis measure pattern, as you would clear filters other than Materiale. But if it works for you that's fine

 

Orders with both products = 
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[Order number] );
    CALCULATETABLE (
        SUMMARIZE ( 'Sales'; Sales[Order number] );
        ALL ( 'Sales' );
        USERELATIONSHIP ( 'Sales'[Materials]; Materiale_Filter[Materials] );
        EXCEPT ( Materiale_Filter; Materiale )
    )
)



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

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
OwenAuger Community Champion
Community Champion

Re: Modified 'Basket Analysis' - don't count when Item = Filter Item

Hi @Anonymous

 

Add EXCEPT ( Materiale_Filter; Material ) to CALCULATETABLE - see below.

 

By the way, I would have expected ALL ( Materiale ) rather than ALL ( 'Sales' ) in the standard Basket Analysis measure pattern, as you would clear filters other than Materiale. But if it works for you that's fine

 

Orders with both products = 
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[Order number] );
    CALCULATETABLE (
        SUMMARIZE ( 'Sales'; Sales[Order number] );
        ALL ( 'Sales' );
        USERELATIONSHIP ( 'Sales'[Materials]; Materiale_Filter[Materials] );
        EXCEPT ( Materiale_Filter; Materiale )
    )
)



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

Proud to be a Datanaut!




View solution in original post

buchisar
Frequent Visitor

Re: Modified 'Basket Analysis' - don't count when Item = Filter Item

 

Can you help me understand if it is possible to add a date dimension to it like in the below table?

 

Thumbnail

 

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.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors