Conditional DISTINCTCOUNT of customer with NON-ZERO balances

[mturcotte]

I need help with a measure for DISTINCTCOUNT of Customer_ID for customers with Non-Zero Balances on transactions for a selected time period.

I started from the default vanilla measure of #Customers := DISTINCTCOUNT('Customer'[Customer_ID]) and also a more elaborate measure as follows:

#Customers2 :=
VAR SelectedDates = VALUES ( 'Date'[Date] )
VAR TransactionsToDate = FILTER ( 'Transaction', 'Transaction'[Date] IN SelectedDates )
VAR Positions =
SUMMARIZE (
TransactionsToDate,
'Transaction'[Date],
'Transaction'[Customer_ID],
'Transaction'[Account_ID],
'Transaction'[Product_ID] )
VAR PositionsWithAmounts =
ADDCOLUMNS (
Positions ,
"Amount", SUM ( 'Transaction'[Amount - RAW] )
)
VAR NonZeroBalancePositions =
FILTER (
PositionsWithAmounts,
[Amount] <> 0
)
VAR Result =
CALCULATE (
DISTINCTCOUNT( 'Transaction'[Customer_ID] ),
NonZeroBalancePositions
)
RETURN Result

Unfortunately, I can figure out what else to try for now and I'm running out of time...

This type of problem must be frequent in finance, but I could not find an example for it.

Here are links to the sample report I crafted to illustrate and explain the problem I need to resolve:

https://atipsolutions-my.sharepoint.com/:u:/g/personal/mturcotte_atipsolutions_onmicrosoft_com/Ecf6H... 

And here's the link to the underlying data set I created for this purpose:

https://atipsolutions-my.sharepoint.com/:x:/g/personal/mturcotte_atipsolutions_onmicrosoft_com/ER_NN... 

Any help suggestion will be much appreciated!

MT

[v-yuta-msft]

@mturcotte ,

The sample pbix seems to be created in an earlier version, please updated power bi desktop to the latest version. In addtion, it seems you have created some temp table using var. This will cost efficiency, I would suggest you refer to doc below to improve your model.

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

https://www.sqlbi.com/articles/introducing-the-power-bi-performance-analyzer/

Community Support Team _ Jimmy Tao

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

[mturcotte]

@v-yuta-msft

This report was created with the LATEST version of Power BI Desktop Optimized for Report Server!

This is *NOT* destined to be used in the cloud service YET (later when my customer will be ready to move to the cloud service, this report will be redited with the latest CLOUD version of PBI Desktop).

I was hoping for some advice on how to get the measure to work at all, not to improve performance over a non-working version...

[mturcotte]

I have made *SOME* progress using DAX Studio... The following code (which surely could be optimized) produces a table with the results I need (but not in a scalar measure as I need it; I need a scalar measure that provides the DISTINCTCOUNT of the customers with non-zero balance for each product):

EVALUATE 
VAR TransactionDates = DATESBETWEEN ( 'Transaction'[Date], DATE ( 2019, 11, 01 ), DATE ( 2019, 11, 10 ) )
VAR TransactionsToDate = -- Start by filtering on the selected dates
    CALCULATETABLE ( 
        ALL (
            'Transaction'[Date],
            'Transaction'[Customer_ID], 
            'Transaction'[Account_ID],
            'Transaction'[Product_ID]
        ), 
        TransactionDates 
    ) 
VAR Positions = -- Then keep only what’s needed for the GROUPBY
    SELECTCOLUMNS ( 
        TransactionsToDate,
        "Customer_ID", ''[Customer_ID], 
        "Account_ID", ''[Account_ID],
        "Product_ID", ''[Product_ID]
    )
VAR PositionsWithAmounts = -- Add the balances for each
    ADDCOLUMNS (
        SUMMARIZE (
            Positions,
            ''[Customer_ID],
            ''[Account_ID],
            ''[Product_ID]
        ),
        "Amount", [Amount]
    )
VAR NonZeroBalancePositions = -- Filter out ZERO balances
    FILTER (
        PositionsWithAmounts,
        ''[Amount] <> 0
    )
VAR NonZeroBalanceProductPositions = -- Group by products & customers
    ADDCOLUMNS (
        SUMMARIZE (
            NonZeroBalancePositions,
            ''[Customer_ID],
            ''[Product_ID]
        ),
        "Amount", [Amount]
    )
VAR Result = -- Finally get distinct count of customers / product
    GROUPBY (
        NonZeroBalanceProductPositions,
        ''[Product_ID],
        "#Cutomers",COUNTX ( CURRENTGROUP (),''[Customer_ID])
    )
RETURN Result

ORDER BY ''[Product_ID]

…But this code exhibits 2 problems (aside from needing to be optimized, I’m sure):

• It starts with a dependence on fixed dates being fed to it, while I would like these dates to come from filters in the report.
• I need the “#Customers” measure to produce a scalar result to work when simply added as column of a Table on the report alongside the Product_ID (preferably the name from ‘Product’[Product]) and the balance “Amount” (which is a simple SUM ( ‘Transaction’[Amount] ) that already works.)

For now, I'll keep working on this.

But if someone has successfully transformed table producing code from DAX Studio into a measure that produces the right scalar based on the context where it executes (all this while getting its input dates from the report filter context), please feel free to share and spare me some precious time.

Thanks,

MT

[mturcotte · Accepted solution]

I think I have found the solution but I have yet to validate it across many different expanded scenarios (including negative balance scenarios and with more data).

In the spirit of sharing back with the community, here's the code of the measure that behaves as intended (so far):

# Customers =

VAR SelectedDates = VALUES ( 'Date'[Date] )

VAR CurrentProduct = VALUES ( 'Transaction'[Product_ID] )

VAR TransactionsToDate =

    FILTER (

        'Transaction';

        'Transaction'[Date] IN SelectedDates

    )

VAR PositionBalances =

    ADDCOLUMNS (

        ALL(

            'Transaction'[Customer_ID];

            'Transaction'[Account_ID];

            'Transaction'[Product_ID]

        );

        "Balance"; CALCULATE ( [Amount] )

    )

VAR NonZeroBalanceProducts =

    FILTER (

        SELECTCOLUMNS (

            PositionBalances;

            "Customer_ID";[Customer_ID];

            "Product_ID"; [Product_ID];

            "Balance";[Balance]

        );

        [Balance] <> 0

    )

VAR Result =

    CALCULATE (

        DISTINCTCOUNT( 'Transaction'[Customer_ID] );

        NonZeroBalanceProducts

    )

RETURN Result

As usual, any suggestion is welcome (including suggestions for optimization).

Thanks,

MT

[mturcotte · Accepted solution]

I think I have found the solution but I have yet to validate it across many different expanded scenarios (including negative balance scenarios and with more data).

In the spirit of sharing back with the community, here's the code of the measure that behaves as intended (so far):

# Customers =

VAR SelectedDates = VALUES ( 'Date'[Date] )

VAR CurrentProduct = VALUES ( 'Transaction'[Product_ID] )

VAR TransactionsToDate =

    FILTER (

        'Transaction';

        'Transaction'[Date] IN SelectedDates

    )

VAR PositionBalances =

    ADDCOLUMNS (

        ALL(

            'Transaction'[Customer_ID];

            'Transaction'[Account_ID];

            'Transaction'[Product_ID]

        );

        "Balance"; CALCULATE ( [Amount] )

    )

VAR NonZeroBalanceProducts =

    FILTER (

        SELECTCOLUMNS (

            PositionBalances;

            "Customer_ID";[Customer_ID];

            "Product_ID"; [Product_ID];

            "Balance";[Balance]

        );

        [Balance] <> 0

    )

VAR Result =

    CALCULATE (

        DISTINCTCOUNT( 'Transaction'[Customer_ID] );

        NonZeroBalanceProducts

    )

RETURN Result

As usual, any suggestion is welcome (including suggestions for optimization).

Thanks,

MT