Forum Discussion

VBAmazing's avatar
VBAmazing
Frequent Visitor
1 year ago
Solved

Count Distinct Customer IDs for New Customers using Measures Only

Hi All,

 

This is my first post in this forum; I have done research ahead of time and found no questions that match my current use case exactly.

 

I am working within a star schema and need to:

  • Count DISTINCT Customer IDs in my Customer table where the FIRST ORDER DATE for a particular Customer & Supplier falls within the filter context (I have a SUPPLIER and DATE slicer)
  • Using ONLY CALCULATIONS; at this point I don't have the option of modifying the underlying data model
  • Display the resultant calculation in both a table alongside other columns, and on its own as a card

I am having difficulty conceptualizing how best to do this; in cases where the model could be edited, you would be able to add a calculated column containing the minimum date for a particular supplier & simply compare that date to the min/max slicer date in context. 

 

Data Model:

  • Model follows a star schema with one:many relationships between Dimension & Fact tables 
  • The tables in the model:
    • Sales (Fact)
    • Date (Dimension)
    • Supplier (Dimension)
    • Customer (Dimension)

The data model is sensitive and I'll be unable to share my PBIX or Data Model screenshots

 

So far, I have tried the following:

Does not meet requirement

 

When placed in a table alongside suppliers, correctly returns 1 or 0 for all Customer / Suppliers whose minimum date falls in context

 

Unable to use as a filter to count distinct Customer IDs within a card; when other fields are removed from a card with this calculation set to 1, DISTINCTCOUNT( Customer ID ) counts all Customer IDs in the dataset.

MinCustomerDate_IsInContext :=
VAR minContextDate  = CALCULATE( MIN( Dates[Date] ), ALLSELECTED('Dates') )
VAR maxContextDate  = CALCULATE( MAX( Dates[Date] ), ALLSELECTED('Dates') )

VAR minSupplierDate =
CALCULATE(
    MIN( Sales[DeliveryDate] ),
    REMOVEFILTERS( Dates[Date] )
)

VAR result = IF( minSupplierDate >= minContextDate && minSupplierDate <= maxContextDate, 1, 0 )

RETURN result
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, VBAmazing 

    Based on your information, I create sample tables:

    Create a new table named Date:

    Date = CALENDAR(MIN('Sale'[DeliveryDate]),MAX('Sale'[DeliveryDate]))

     

    Then create new measures:

    FirstOrderDate = 
CALCULATE(
    MIN('Sale'[DeliveryDate]),
    ALLEXCEPT('Sale', 'Sale'[CustomerID], 'Sale'[SupplierID])
)
    IsFirstOrderDateInContext = 
VAR minContextDate = MIN('Date'[Date])
VAR maxContextDate = MAX('Date'[Date])
RETURN
IF(
    [FirstOrderDate] >= minContextDate && [FirstOrderDate] <= maxContextDate,
    1,
    0
)
    DistinctNewCustomers = 
CALCULATE(
    DISTINCTCOUNT('Customer'[CustomerID]),
    FILTER(
        'Customer',
        [IsFirstOrderDateInContext] = 1
    )
)

     

    Put these measures in table visual, here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, VBAmazing 

    Based on your information, I create sample tables:

    Create a new table named Date:

    Date = CALENDAR(MIN('Sale'[DeliveryDate]),MAX('Sale'[DeliveryDate]))

     

    Then create new measures:

    FirstOrderDate = 
CALCULATE(
    MIN('Sale'[DeliveryDate]),
    ALLEXCEPT('Sale', 'Sale'[CustomerID], 'Sale'[SupplierID])
)
    IsFirstOrderDateInContext = 
VAR minContextDate = MIN('Date'[Date])
VAR maxContextDate = MAX('Date'[Date])
RETURN
IF(
    [FirstOrderDate] >= minContextDate && [FirstOrderDate] <= maxContextDate,
    1,
    0
)
    DistinctNewCustomers = 
CALCULATE(
    DISTINCTCOUNT('Customer'[CustomerID]),
    FILTER(
        'Customer',
        [IsFirstOrderDateInContext] = 1
    )
)

     

    Put these measures in table visual, here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • VBAmazing's avatar
    VBAmazing
    Frequent Visitor
    1 year ago

    This does exactly what I need. Thank you for your reply!

     

    Conceptually it makes a lot of sense and clarified some concepts for me. Super valuable response.