Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Distinct Count wrong in Matrix

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:

 2019-11-08 10_41_36-Test Case - Power BI Desktop.png

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.
2019-11-08 10_48_46-Test Case - Power BI Desktop.png
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
1 REPLY 1
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors