Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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:



Used Model:

Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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 

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      buchisar
      Frequent Visitor
      8 years ago

       

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

       

      Thumbnail