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
Super User

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
Super User

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.

 

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!