Number of Unique Customers with Exclusions and Inclusions

[Anonymous]

HI All, I have a Calendar table and Customer Invoice Table.   I am a very new recruit to Dax.

I am tasked with, for any date range, determining the number of Unique Customers and total Tons that were sold to Customers with the following conditions:

• # of Customers and Tons that had 'Flat Rolled' but not 'Long Product' sales
• # of Customers and Tons that had 'Long Product' but not 'Flat Rolled' sales
• # of Customers and Tons that had 'Flat Rolled' and 'Long Product' sales

I dont have the dax experience to get to the correct numbers (I verified numbers for August 2020 in sql), and have tried combined calculated tables with filters, separate calculated tables by PRODUCT_TYPE with filters and isblank, but at this point i am just thrashing and i am gettting nonsense for results.

Any help would be immensely appreciated!  I need to be taken by the hands and guided through this.

Thanks very much in advance.  This will be a great learning experience.

The customer invoice table is related to the calendar by way of an Invoice Date (many invoices-to-1 calendar date) if that is helpful.

Each row of the Invoice has the following columns, with sample base detail data for August 2020 included below:

CUSTOMER_ID	INVOICE_DATE	PRODUCT_TYPE	TONS
100794	8/10/2020	Flat Rolled	1.89375
100794	8/10/2020	Long Product	0.37632
100794	8/10/2020	Long Product	0.544919
100794	8/28/2020	Long Product	0.199945
100841	8/10/2020	Flat Rolled	0.441
100853	8/19/2020	Flat Rolled	0.58647
100853	8/5/2020	Flat Rolled	1.509
101036	8/12/2020	Flat Rolled	0.25825
101036	8/19/2020	Flat Rolled	0.120455
101036	8/3/2020	Flat Rolled	1.0635
101052	8/14/2020	Flat Rolled	0.4375
101052	8/7/2020	Flat Rolled	0.302832
101052	8/7/2020	Flat Rolled	0.27309
101052	8/14/2020	Flat Rolled	0.2025
101052	8/7/2020	Flat Rolled	0.192352
101052	8/7/2020	Flat Rolled	1.72368
101052	8/4/2020	Flat Rolled	0.172115
101052	8/12/2020	Long Product	0.237584
101052	8/4/2020	Long Product	0.84487
101190	8/12/2020	Flat Rolled	0.4368
101190	8/10/2020	Long Product	0.313515
101190	8/18/2020	Long Product	0.367003
101190	8/28/2020	Long Product	0.790013
101190	8/28/2020	Long Product	0.409
101284	8/26/2020	Flat Rolled	1.820025
101284	8/14/2020	Flat Rolled	0.53975
101284	8/14/2020	Flat Rolled	0.787125
101284	8/3/2020	Flat Rolled	1.88799
101284	8/14/2020	Flat Rolled	0.882
101284	8/21/2020	Flat Rolled	1.164
101284	8/7/2020	Flat Rolled	0.141765
101284	8/18/2020	Flat Rolled	0.608
101284	8/21/2020	Flat Rolled	1.46499
101284	8/18/2020	Flat Rolled	0.30866
101284	8/4/2020	Flat Rolled	0.763528
101284	8/19/2020	Flat Rolled	1.46499
101284	8/26/2020	Flat Rolled	0.75605
101284	8/7/2020	Flat Rolled	0.540155
101284	8/12/2020	Flat Rolled	1.72722
101284	8/4/2020	Flat Rolled	0.312375
101284	8/31/2020	Flat Rolled	0.6074
101284	8/28/2020	Flat Rolled	1.46499
101466	8/14/2020	Flat Rolled	0.485575
101468	8/17/2020	Flat Rolled	1.1805
101468	8/10/2020	Flat Rolled	1.5235
101468	8/17/2020	Flat Rolled	1.87545
101468	8/5/2020	Flat Rolled	0.96
101468	8/19/2020	Flat Rolled	1.534075
101468	8/25/2020	Flat Rolled	1.5252
101468	8/3/2020	Flat Rolled	2.034
101624	8/18/2020	Flat Rolled	0.3024
101624	8/4/2020	Flat Rolled	0.14655
101624	8/4/2020	Flat Rolled	0.060715
101624	8/18/2020	Flat Rolled	0.361818
101624	8/18/2020	Long Product	0.07908
102111	8/31/2020	Flat Rolled	0.087587
102111	8/5/2020	Flat Rolled	0.068273
102168	8/12/2020	Long Product	0.030811

[Anonymous]

// With the table you have... call it T.
// You should also have a Calendar table
// that you'd connect via 1:* on [Date]
// to T[invoice_date]. Customer should
// also be a separate dimension.
// Product or 'Product Type', depending
// on what your model really contains,
// should also be a separate dimension.
// Please, read upon the star schema and
// its importance for modeling data for
// Power BI.

// # Cust that had Flat Rolled but not
// Long Product sales in the selected
// time frame. Time frames can only be
// selected from the calendar. Fact
// tables should always be hidden.
[# Cust (FR\LP)] = 
// First, get the id's of the customers
// that did have at least one entry
// of Flat Rolled in the fact table
// but no LP entry. Since there are
// only two possible entries, the formula
// below does the job.
var __custsFrNoLp =
    EXCEPT(
        // This bit gets all customers
        // that exist in the current
        // context.
        DISTINCT( T[customer_id] ),
        // This bit gets all customers
        // that have at least one entry
        // of LP in the current context.
        CALCULATETABLE(
            DISTINCT( T[customer_id] ),
            KEEPFILTERS( 
                'Product Type'[ProductTypeId]
                    = "Long Product"
            )
        )
    )
var __output =
    COUNTROWS( __custsFrNoLp )
return
    __output
    
// Here is the tons for the above customers...
// For the other sets of customers, you'll
// just copy this pattern with the obvious
// changes.
[Tons (FR\LP)] =
var __custsFrNoLp =
    EXCEPT(
        // This bit gets all customers
        // that exist in the current
        // context.
        DISTINCT( T[customer_id] ),
        // This bit gets all customers
        // that have at least one entry
        // of LP in the current context.
        CALCULATETABLE(
            DISTINCT( T[customer_id] ),
            KEEPFILTERS( 
                'Product Type'[ProductTypeId]
                    = "Long Product"
            )
        )
    )
var __output =
    CALCULATE(
        SUM( T[Tons] ),
        TREATAS(
            __custsFrNoLp,
            Customer[customer_id]
        ),
        ALL( Customer )
    )
return
    __output

    
// In the same vain, you can get the
// custs that had LP but no FR
[# Cust (LP\FR)] = 
var __custsLpNoFr =
    EXCEPT(
        DISTINCT( T[customer_id] ),
        CALCULATETABLE(
            DISTINCT( T[customer_id] ),
            KEEPFILTERS( 
                'Product Type'[ProductTypeId]
                    = "Flat Rolled"
            )
        )
    )
var __output =
    COUNTROWS( __custsLpNoFr )
return
    __output

// And here are the ones that did have
// both LP and FR.
[# Custs LP & FR] =
var __custsLpAndFr =
    INTERSECT(
        CALCULATETABLE(
            DISTINCT( T[customer_id] ),
            KEEPFILTERS( 
                'Product Type'[ProductTypeId]
                    = "Long Product"
            )            
        ),
        CALCULATETABLE(
            DISTINCT( T[customer_id] ),
            KEEPFILTERS( 
                'Product Type'[ProductTypeId]
                    = "Flat Rolled"
            )
        )
    )
var __output =
    COUNTROWS( __custsLpAndFr )
return
    __output

And here's the article that you HAVE to read about star schema and why it's of utmost importance in Power BI:

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

[OwenAuger · Accepted solution]

Hi @Anonymous

Hi @amartin141

The category of measures you are wanting is Basket Analysis.

I have based my answer here on the Basket analysis page on DAX Patterns.
https://www.daxpatterns.com/basket-analysis/

I have attached a PBIX with sample measures using your data. I have not worried about the Date table in my PBIX as it is not essential to the Basket Analysis setup, but  

The steps I followed are:  

1. Set up your data model with a Product table and a secondary Product table. On DAX Patterns they call these tables 'Product' and 'And Product'. In my example I called them 'Product A' and 'Product B'. These are both related to your main Sales table (I named this table Invoice) with an inactive relationship with the secondary table.
   
   
2. Create measures as follows:

// BASE # CUSTOMERS MEASURE //
# Customers = 
SUMX ( SUMMARIZE ( Invoice, Invoice[CUSTOMER_ID] ), 1 )
// This is equivalent to DISTINCTCOUNT of CUSTOMER_ID
// but DAX Patterns explains why to consider using this instead.

// CUSTOMERS WITH BOTH PRODUCTS (INTERNAL) MEASURE //
# Customers with Both Products (Internal) =
VAR CustomersWithProductB =
    CALCULATETABLE (
        SUMMARIZE ( Invoice, Invoice[CUSTOMER_ID] ),
        REMOVEFILTERS ( 'Product A' ),
        REMOVEFILTERS ( Invoice[PRODUCT_TYPE] ),
        USERELATIONSHIP ( Invoice[PRODUCT_TYPE], 'Product B'[Product Type B] )
    )
VAR Result =
    CALCULATE ( [# Customers], KEEPFILTERS ( CustomersWithProductB ) )
RETURN
    Result


// CUSTOMERS WITH BOTH PRODUCTS FINAL MEASURE //
// Returns blank if same product selected on both A & B
# Customers with Both Products =
IF (
    ISEMPTY (
        INTERSECT (
            DISTINCT ( 'Product A'[Product Type A] ),
            DISTINCT ( 'Product B'[Product Type B] )
        )
    ),
    [# Customers with Both Products (Internal)]
)

// CUSTOMERS WITH PRODUCT A BUT NOT PRODUCT B MEASURE //
# Customers with Product A & Not Product B =
VAR CustomersWithProductB =
    CALCULATETABLE (
        SUMMARIZE ( Invoice, Invoice[CUSTOMER_ID] ),
        REMOVEFILTERS ( 'Product A' ),
        REMOVEFILTERS ( Invoice[PRODUCT_TYPE] ),
        USERELATIONSHIP ( Invoice[PRODUCT_TYPE], 'Product B'[Product Type B] )
    )
VAR CustomersWithoutProductB =
    EXCEPT ( ALL ( Invoice[CUSTOMER_ID] ), CustomersWithProductB )
VAR Result =
    CALCULATE ( [# Customers], KEEPFILTERS ( CustomersWithoutProductB ) )
RETURN
    Result

I also created similar measures for Tons. With Tons there is an additional measure I created to sum Tons for both products as well as just Product A. 

Then, for example, you can include slicers on 'Product A' and 'Product B' that let you filter as required. 

You may need to adjust the exact behaviour, but hopefully that helps as a starting point 🙂