topic Re: Distinct Count of non-zero records in DAX Commands and Tips

Distinct Count of non-zero records

onape — Thu, 16 Jan 2025 16:01:26 GMT

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

Re: Distinct Count of non-zero records

johnt75 — Thu, 16 Jan 2025 16:40:27 GMT

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.

Re: Distinct Count of non-zero records

sanalytics — Fri, 17 Jan 2025 07:58:44 GMT

onape
PLEASE DO NOT PROVIDE THE SCREENSHOT.

Re: Distinct Count of non-zero records

onape — Fri, 17 Jan 2025 11:19:09 GMT

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?

Re: Distinct Count of non-zero records

johnt75 — Fri, 17 Jan 2025 11:31:11 GMT

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

Re: Distinct Count of non-zero records

onape — Fri, 17 Jan 2025 11:53:02 GMT

Thank you again.

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

Re: Distinct Count of non-zero records

johnt75 — Fri, 17 Jan 2025 12:13:07 GMT

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

Re: Distinct Count of non-zero records

onape — Fri, 17 Jan 2025 16:01:31 GMT

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

Re: Distinct Count of non-zero records

johnt75 — Fri, 17 Jan 2025 16:24:17 GMT

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

Re: Distinct Count of non-zero records

onape — Sun, 19 Jan 2025 06:11:47 GMT

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]

Re: Distinct Count of non-zero records

Anonymous — Mon, 20 Jan 2025 09:28:44 GMT

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

Re: Distinct Count of non-zero records

johnt75 — Mon, 20 Jan 2025 09:58:58 GMT

Is your date table marked as a date table ?

Is there a relationship between your date table and Sheet1?

Re: Distinct Count of non-zero records

onape — Mon, 20 Jan 2025 13:55:51 GMT

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

Re: Distinct Count of non-zero records

johnt75 — Mon, 20 Jan 2025 14:28:24 GMT

Try unticking "Show items with no data"

Re: Distinct Count of non-zero records

onape — Tue, 21 Jan 2025 15:42:19 GMT

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?

Re: Distinct Count of non-zero records

onape — Tue, 21 Jan 2025 15:44:06 GMT

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

Re: Distinct Count of non-zero records

johnt75 — Tue, 21 Jan 2025 16:18:43 GMT

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.

Re: Distinct Count of non-zero records

Anonymous — Wed, 22 Jan 2025 05:34:40 GMT

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

Re: Distinct Count of non-zero records

onape — Wed, 22 Jan 2025 13:25:11 GMT

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

Re: Distinct Count of non-zero records

onape — Wed, 22 Jan 2025 13:29:21 GMT

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.