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

[Anonymous]

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:

Used Model:

[OwenAuger · Accepted solution]

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

s 

[buchisar]

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