topic Re: DistinctCount with individual Rows Summing incorrectly in DAX Commands and Tips

DistinctCount with individual Rows Summing incorrectly

mclawler — Mon, 12 Feb 2024 18:42:38 GMT

**Moderators, please remove the previous thread I created titled "Totals not calculating correctly using SumX" as I have dug much deeper into this issue and realized the actual root of the problem.**

I have concluded that my Matrix Table is calculating the correct amount of DistinctCount(MemberNumber) Total. However, there is 1 MemberNumber that exists for both results "Existing" & "New" within the calculcated column. Therefore, when my DAX is using DistinctCount for the entire column it gets the correct result of 468. But, when it is using DistinctCount for each result "Existing" & "New" as separate rows, it's not seeing that overlapping MemberNumber and counts it as an additional DisctinctCount within the "New" row. Please advise how to make each Row's filter/isolation take into account the entire Column's data.

Current DAX:

MemberNumberDISTINCTCOUNT =

SUMX( VALUES(Heloc3MonthAdvances[TransactionDateMonth]),

DISTINCTCOUNT ( Heloc3MonthAdvances[MemberNumber] ))

The end result should read:

Existing = 433

New = 35

Total = 468

This is the current visual:

Thank you!!

Re: DistinctCount with individual Rows Summing incorrectly

lbendlin — Tue, 13 Feb 2024 01:06:43 GMT

Use EXCEPT to weed out the extra member in New.

Re: DistinctCount with individual Rows Summing incorrectly

Anonymous — Tue, 13 Feb 2024 06:28:45 GMT

Hi mclawler

lbendlin Thank you very much for sharing!

This is a very suitable way, here I provide some specific code. You can try the following code:

MemberNumberDISTINCTCOUNT = SUMX ( VALUES ( Heloc3MonthAdvances[TransactionDateMonth] ), DISTINCTCOUNT ( Heloc3MonthAdvances[MemberNumber] ) )

MemberNumberDISTINCTCOUNT_Existing = CALCULATE ( [MemberNumberDISTINCTCOUNT], Heloc3MonthAdvances[Result] = "Existing" )

MemberNumberDISTINCTCOUNT_New = CALCULATE ( [MemberNumberDISTINCTCOUNT], Heloc3MonthAdvances[Result] = "New", EXCEPT ( VALUES ( Heloc3MonthAdvances[MemberNumber] ), CALCULATETABLE ( VALUES ( Heloc3MonthAdvances[MemberNumber] ), Heloc3MonthAdvances[Result] = "Existing" ) ) )

This should give you the results you want, which is 433 for "Existing" and 35 for "New".

EXCEPT function (DAX) - DAX | Microsoft Learn

Regards,

Nono Chen

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

Re: DistinctCount with individual Rows Summing incorrectly

mclawler — Tue, 13 Feb 2024 16:25:20 GMT

Thank you for the reply I think you're on the right track but it's giving me blanks. Currently I use a calculated column to generate the rows for Existing/New:

Existing/New = IF(Heloc3MonthAdvances[OpenDateMonth] < Heloc3MonthAdvances[TransactionDateMonth],"Existing HELOC","New HELOC")

Any idea how to modify your code to adapt to my rows? I have to keep my rows becuase in the same table I use other measures for $ Sum and YoY, MoM, etc... Thank you so much for your help!

I tried these with no success:

MemberNumberDISTINCTCOUNT_Existing =

CALCULATE (

[MemberNumberDISTINCTCOUNT],

Heloc3MonthAdvances[Existing/New] = "Existing"

)

MemberNumberDISTINCTCOUNT_New =

CALCULATE (

[MemberNumberDISTINCTCOUNT],

Heloc3MonthAdvances[Existing/New] = "New",

EXCEPT (

VALUES ( Heloc3MonthAdvances[MemberNumber] ),

CALCULATETABLE (

VALUES ( Heloc3MonthAdvances[MemberNumber] ),

Heloc3MonthAdvances[Existing/New] = "Existing"

)

Re: DistinctCount with individual Rows Summing incorrectly

mclawler — Tue, 13 Feb 2024 16:26:10 GMT

Please see my reponse above, after reading what EXCEPT can do I believe you're right but can't figure out the proper code. Please advise, and thank you for your help!

Re: DistinctCount with individual Rows Summing incorrectly

lbendlin — Tue, 13 Feb 2024 18:09:29 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523