Distinct Count wrong in Matrix

[Anonymous]

Hello,

This issue has been bothering me for a while...of course it's DAX, and context (i'm a heavy SQL user).  Here comes the scenario:

Goal: To report distinct counts of P_id with the event Y per reporting month, break down by stage. Per month technically means by the end of the month.

Data model: two tables disconnected (transaction table, calendar table)

Transaction Data:

 

Measure:

Y Count =

VAR varTargetEvent = "Y"

VAR varMaxDate = MAX('Calendar'[Date])

VAR varBase = FILTER('Transactions',

'Transactions'[Date] < varMaxDate + 1

)

VAR varMax = SUMMARIZE(

    varBase,

    'Transactions'[P_Id],

    "_max_createdDate", MAX('Transactions'[Date])

)

VAR varFlt = CALCULATETABLE(

    'Transactions',

    TREATAS(varMax, 'Transactions'[P_Id], 'Transactions'[Date])

)

VAR varLS = FILTER(

    varFlt,

    'Transactions'[Event] = varTargetEvent

)

VAR varTotal = CALCULATE(

DISTINCTCOUNT('Transactions'[P_Id]),

varLS

)

RETURN varTotal

Actual Result: while the total column is correct, the break down columns (B/C/D) are wrong.

Expected Result: in the matrix

2019-07: 1xB

2019-08: 1xB, 1xC

2019-09: 1xB, 1xD

2019-10: 1xB

Highly appreciate any insights/suggestions!

Regards,
Handel

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Or MM3TR&R: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...