Distinct Count of non-zero records

[onape]

Hello, I'm hoping I could get some help with something I'm working on. I have a dataset that has values of customers investment per day. some days may be missing from the dataset. My visual has a date slicer (mm-yyyy). I need to do a distinct count of customer ID by Type in my table visual such that only last non-zero value amount on or before the selected date are counted.

The right hand side shows records that will be counted on not based on the filter

so for example, if April 2024 is selected in my filter , the result will be

I have tried a numberof DAX but none is working correctly . Most of them do not count a record if the value date is not in the month of the date filter.

Any help with this will be highly appreciated. 

Thank you

[Anonymous]

Hi @onape ,

My approach is to use CROSSJOIN to create an intermediate table that counts whether the Type corresponding to each customer ID has an Amount of 0 at the maximum date, returns 0 if it does, and 1 otherwise, and then uses a matrix and utilizes subtotals on the rows to get the result you want. Here is my test and the pbix file for your reference.

Step1. Custom Table.

Table 3 = CROSSJOIN(VALUES('Table'[Customer ID]),VALUES('Table'[Type]))

Step2. Create measures.

1 = 
VAR _selectedMonth = MAX('Table 2'[Date])

VAR _maxdate = CALCULATE(MAX('Table'[Value Date]),ALL('Table'),'Table'[Customer ID]=MAX('Table 3'[Customer ID]),'Table'[Type]=MAX('Table 3'[Type]),'Table'[Value Date]<=_selectedMonth)

VAR _amount = CALCULATE(MAX('Table'[Value Amount]),'Table'[Customer ID]=MAX('Table 3'[Customer ID]),'Table'[Type]=MAX('Table 3'[Type]),'Table'[Value Date]=_maxdate)

VAR _check = IF(_amount>0,1,0)

RETURN
_check

DistCount = SUMX('Table 3',[1])

Best regards,

Mengmeng Li

[onape]

Thank you, @Anonymous . This worked if the 2 original tables (Dates and the details tables) do not have a relationship. For other purposes of the report, I will need them to have a relationship. Do you think there's something else that can be done?

[Anonymous]

Hi @onape ,

Yes, this method requires that the date table is not related to the details table, otherwise the slicer will filter the matrix. You can create another date table for this method, and using your original date table (that is related to the details table) elsewhere in your report.

Best regards,

Mengmeng Li

[onape]

Thanks for your help @Anonymous . This method might not work for me because the users need to see some other metrics on the table visuals which are controlled by the date filter on the page.

[sanalytics]

@onape  
PLEASE DO NOT PROVIDE THE SCREENSHOT.

[johnt75]

I think you can use

Num Customers =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR SummaryTable =
    CALCULATETABLE (
        INDEX (
            1,
            'Table',
            ORDERBY ( 'Table'[Value Date], DESC ),
            PARTITIONBY ( 'Table'[Customer ID] )
        ),
        'Date'[Date] <= ReferenceDate,
        'Table'[Value Amount] > 0
    )
VAR Result =
    COUNTROWS ( SummaryTable )
RETURN
    Result

Because the INDEX function will only produce 1 row per customer, per type, you don't need to do a distinct count, COUNTROWS should suffice.

[onape]

Thank you for the help.

I am currently getting an error though "INDEX's Relation parameter may have duplicate rows. This is not allowed." I'm guessing it's because a customer can have valuations on different or even same account number on the same day. Is there a way to fix this please?

[johnt75]

You may be able to get around that by using the MATCHBY function

Num Customers =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR SummaryTable =
    CALCULATETABLE (
        INDEX (
            1,
            'Table',
            ORDERBY ( 'Table'[Value Date], DESC ),
            PARTITIONBY ( 'Table'[Customer ID] ),
            MATCHBY ( 'Table'[Customer ID], 'Table'[Value Date] )
        ),
        'Date'[Date] <= ReferenceDate,
        'Table'[Value Amount] > 0
    )
VAR Result =
    COUNTROWS ( SummaryTable )
RETURN
    Result

[onape]

Thank you again.

I'm still getting an error message. Please see below.

[johnt75]

Hopefully this will remove the duplicates

Num Customers =
VAR ReferenceDate =
    MAX ( 'Date'[Date] )
VAR BaseTable =
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[Customer ID], 'Table'[Value Date] ),
        'Date'[Date] <= ReferenceDate,
        'Table'[Value Amount] > 0
    )
VAR SummaryTable =
    INDEX (
        1,
        BaseTable,
        ORDERBY ( 'Table'[Value Date], DESC ),
        PARTITIONBY ( 'Table'[Customer ID] ),
        MATCHBY ( 'Table'[Customer ID], 'Table'[Value Date] )
    )
VAR Result =
    COUNTROWS ( SummaryTable )
RETURN
    Result

[onape]

Thank you - This works but if a "Type" has no value at all in the selected month, that type is filtered off the table visual

[johnt75]

Can you use Performance Analyzer to get a copy of the query used for the table visual, and post that.

[onape]

Hello,

Please see the query below. Thanks.

// DAX Query

DEFINE

VAR __DS0FilterTable = 

TREATAS({"Aug 2024"}, 'Calendar Table'[Year-Month])

VAR __DS0Core = 

SUMMARIZECOLUMNS(

ROLLUPADDISSUBTOTAL('Sheet1'[Type], "IsGrandTotalRowTotal"),

__DS0FilterTable,

"Num_Customer", '_SampleTableMeasure'[Num Customer]

)

VAR __DS0PrimaryShowAll = 

ADDMISSINGITEMS(

'Sheet1'[Type],

__DS0Core,

ROLLUPISSUBTOTAL('Sheet1'[Type], [IsGrandTotalRowTotal]),

__DS0FilterTable,

CALCULATETABLE(

DISTINCT('Sheet1'[Type]),

KEEPFILTERS(__DS0FilterTable)

)

)

VAR __DS0PrimaryWindowed = 

TOPN(502, __DS0PrimaryShowAll, [IsGrandTotalRowTotal], 0, 'Sheet1'[Type], 1)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

[IsGrandTotalRowTotal] DESC, 'Sheet1'[Type]

[johnt75]

Is your date table marked as a date table ?

Is there a relationship between your date table and Sheet1? 

[onape]

Yes, my calendar table is marked as a date table and there's a relationship between it and Sheet1.

[johnt75]

Try unticking "Show items with no data"

[onape]

Unfortunately, It doesn't make a difference in the results.

[johnt75]

Can you post a sample PBIX with any confidential data removed, or a spreadsheet with the raw data? You can share from OneDrive or Google Drive or similar.

[onape]

Not quite sure of how attachments work here but I've included the link below. Please let me know if that works okay for you. Thanks.

PBIX File  

[johnt75]

The relationship with the date table was adding a filter which was messing up the INDEX function. We need to add REMOVEFILTERS

Num Customers = 
Var ReferenceDate = MAX('Calendar Table'[Date])

Var BaseTable = 
CALCULATETABLE(
    SUMMARIZE(Sheet1,Sheet1[Customer ID],Sheet1[Value Date]),
    'Calendar Table'[Date] <=ReferenceDate,
    Sheet1[Value Amount] >0
)

VAR SummaryTable = 
CALCULATETABLE(
    INDEX(
        1,
        BaseTable,
        ORDERBY(Sheet1[Value Date],DESC),
        PARTITIONBY(Sheet1[Customer ID]),
        MATCHBY(Sheet1[Customer ID],Sheet1[Value Date])
    ),
	REMOVEFILTERS( 'Calendar Table' )
)
VAR Result = 
    COUNTROWS(SummaryTable)
	

RETURN
    Result