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

Row total count and grand total count is diffrent by using DistinctCount and Calculate in Matrix

I am actively looking for the solution to resolve the issue appeared several times in my Power BI report. So I have created the measure to calculate the distinct count of the users which are connected City/country-wise by using below DAX.

Total User Connected(Wireless) = CALCULATE( DISTINCTCOUNT('Posture assessment'['ENDPOINT_ID']),FILTER('Posture assessment','Posture assessment'[NAC Policy ]="Wireless"))

And my grand total and subtotal is completely wrong as compared to actual row count. Then I tried a different approach with this DAX below but no change. Total is still wrong.

Count of Unique = SUMX(Values('Posture assessment'[Region]),CALCULATE(DISTINCTCOUNT('Posture assessment'['ENDPOINT_ID']),FILTER('Posture assessment','Posture assessment'[NAC Policy ]="Wireless")))

Table illustration is: Here, for example, India's value is different as compared to the total.Capture.GIF

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Try to rollup from city level

Count of Unique = SUMX(summarize('Posture assessment','Posture assessment'[Region],'Posture assessment'[Country],'Posture assessment'[City],"_1",CALCULATE(DISTINCTCOUNT('Posture assessment'['ENDPOINT_ID']),FILTER('Posture assessment','Posture assessment'[NAC Policy ]="Wireless"))),[_1])

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , Try to rollup from city level

Count of Unique = SUMX(summarize('Posture assessment','Posture assessment'[Region],'Posture assessment'[Country],'Posture assessment'[City],"_1",CALCULATE(DISTINCTCOUNT('Posture assessment'['ENDPOINT_ID']),FILTER('Posture assessment','Posture assessment'[NAC Policy ]="Wireless"))),[_1])
Anonymous
Not applicable

Thanks for the quick help Amit, It is working. 

Just wanted to know why we are using "_1". Is this to calculate row or some kind of indexing. 

Please explain. 

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.