DistinctCount with individual Rows Summing incorrectly

[mclawler]

**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!! 

[Anonymous]

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.

[mclawler]

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"

        )

    )

)

[lbendlin]

Use EXCEPT to weed out the extra member in New.

[mclawler]

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! 

[lbendlin]

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-...

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/1447...