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.

 

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

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

Top Solution Authors
Top Kudoed Authors