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.

 

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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.