cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerdriveX6
Frequent Visitor

Distinct count of min by category

Dear Gurus,
I have stucked on how to get distinctcount of minimum by category.
Sample of source data shared at GoogleDrive.

 

I build visualisation via matrix and got distinctcount of all StateExt by Components.
But how can i get MIN value of each Area/Family from within all this Area/Family components?

In filtered sample, by required solution logic its should give me Area_A/Family_1/1_Top = 22, Area_A/Family_1/2_Mid=1

Pic.1Pic.1

 

 

 

 

 

In other words, i want to remove from matrix row Component and instead of 89 for 1_Top get 22 and for 2_Mid instead 20 get 1 (as a minimals from the logic in previous sentence):

Pic.2Pic.2

 

 

 

 

 

I have look over this forum and found some similar requests, but its differ a little and im as newbie for such deals stucked in adopt solutions for my case.

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

Hi @PowerdriveX6 

Create a measure and place it in the last visual. See it all at work in the attached file.

 

Measure =
MINX (
    DISTINCT ( SourceTable[Component] ),
    CALCULATE ( DISTINCTCOUNT ( SourceTable[EquipmentID] ) )
)

 

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

2 REPLIES 2
AlB
Super User III
Super User III

Hi @PowerdriveX6 

Create a measure and place it in the last visual. See it all at work in the attached file.

 

Measure =
MINX (
    DISTINCT ( SourceTable[Component] ),
    CALCULATE ( DISTINCTCOUNT ( SourceTable[EquipmentID] ) )
)

 

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

PowerdriveX6
Frequent Visitor

Work perfect, already adopted to original model.
My error was in wrong wrapping of Distinct and Calculate into MinX, now i got logic of this measure.
Thanks a lot, @AlB !

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors