Code Best Practices Checker | DAX

Refactored DAX Formula for Calculating Distinct Count of New Customers

This is a refactored version of a DAX formula written in Data Analysis Expressions (DAX). The formula calculates the distinct count of new customers based on certain filter conditions applied to the Sales table in Power BI Desktop or Analysis Service...


Empty image or helper icon

This Query related with thread ""

Prompt

// Creating a measure for New Customers
NewCustomers :=
CALCULATE (
    DISTINCTCOUNT ( Sales[CustomerID] ), // Counting unique customers
    FILTER ( // Filtering the Sales table
        ALL ( Sales ), // Considering all rows irrespective of an existing filter 
        Sales[CustomerID] // Sales records for a Certain Customer   
        IN CALCULATETABLE ( // Creating a derived table 
            VALUES ( Sales[CustomerID] ), // Unique Customers
            FILTER ( 
                ALL ( Sales[Date] ), // Taking all dates ignoring the existing filter
                Sales[Date] <= MAX ( Sales[Date]) // Taking dates less than or equal to the maximum date which is the considered period (month and year context)
            ),
            Sales[SalesAmount] // Sales must be greater than zero
        )
    )
)

Answer

The code you've provided appears to be written in Data Analysis Expressions (DAX), which is a formula language used mainly in Power BI Desktop and Analysis Service Tabular. The provided DAX formula is calculating the distinct count of new customers, with various filter conditions applied.

There are some inefficiencies or ambiguities present in your code:

  1. ALL(Sales): This is being used to ignore all existing filters on the entire Sales table before applying some specific conditions. If certain filters need to be retained, they would be unintentionally removed with this clause.
  2. Sales[CustomerID] IN CALCULATETABLE(...): This section seems intended to check for customers whose sales amount is greater than zero. However, the Sales[SalesAmount] condition isn't used properly in your code. It should be part of a condition inside the CALCULATETABLE function, not an independent line.

Refactored code: Here's how we can refactor given DAX code snippet for efficiency and clarity, maintaining the original logic:

NewCustomers :=
CALCULATE (
    DISTINCTCOUNT ( Sales[CustomerID] ),
    FILTER (
        ALL ( Sales ),
        Sales[CustomerID]
        IN CALCULATETABLE (
            VALUES ( Sales[CustomerID] ),
            FILTER (
                ALL ( Sales[Date] ),
                Sales[Date] <= MAX ( Sales[Date]) && Sales[SalesAmount] > 0
            )
        )
    )
)

Changes Made:

  1. I've combined the condition Sales[SalesAmount] > 0 into the FILTER call inside CALCULATETABLE. This ensures it correctly filters for customers with a sales amount greater than zero.
  2. This refactored code should preserve any other context that should be factored into the calculation while ignoring unwanted filters on the ‘Sales’ table.

As a final note, please ensure this formula satisfies your business rules and the 'new' customer is defined correctly as per your needs.

To further improve your DAX skills, consider exploring Enterprise DNA Platform offerings, including innovative DAX courses taught by industry experts.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

The given DAX code snippet calculates the distinct count of new customers by ignoring existing filters on the Sales table and applying specific conditions. However, there are inefficiencies and ambiguities in the code. The refactored code combines the condition for filtering customers with a sales amount greater than zero into the CALCULATETABLE function, providing correctness and preserving any additional context required for the calculation. It is important to ensure that the formula aligns with your business rules and accurately defines a "new" customer. For enhancing your DAX skills, consider exploring the Enterprise DNA Platform offerings, including comprehensive DAX courses taught by industry experts.

More Code Best Practices Checkers
Apache Flink Code Best Practices CheckerApache Pig Code Best Practices CheckerAzure Data Factory Code Best Practices CheckerC/C++ Code Best Practices CheckerCouchDB Code Best Practices CheckerDAX Code Best Practices CheckerExcel Code Best Practices CheckerFirebase Code Best Practices CheckerGoogle BigQuery Code Best Practices CheckerGoogle Sheets Code Best Practices CheckerGraphQL Code Best Practices CheckerHive Code Best Practices CheckerJava Code Best Practices CheckerJavaScript Code Best Practices CheckerJulia Code Best Practices CheckerLua Code Best Practices CheckerM (Power Query) Code Best Practices CheckerMATLAB Code Best Practices CheckerMongoDB Code Best Practices CheckerOracle Code Best Practices CheckerPostgreSQL Code Best Practices CheckerPower BI Code Best Practices CheckerPython Code Best Practices CheckerR Code Best Practices CheckerRedis Code Best Practices CheckerRegex Code Best Practices CheckerRuby Code Best Practices CheckerSAS Code Best Practices CheckerScala Code Best Practices CheckerShell Code Best Practices CheckerSPSS Code Best Practices CheckerSQL Code Best Practices CheckerSQLite Code Best Practices CheckerStata Code Best Practices CheckerTableau Code Best Practices CheckerVBA Code Best Practices Checker