Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Coriel-11
Resolver I
Resolver I

Group by a count of values

This seems simple, but I'm hopeless at DAX and so hours later I'm still no closer

 

I have a single table and I want to create a measure that does the equivalent of an Excel countif of one column, and then group the count result into one of three categories. So a summarised version of my data looks like this:

URL StemPage-Type
/Team01/ 
/Team01/CatchingCatching
/Team01/ThrowingThrowing
/Team01/RunningRunning
/Team02/ 
/Team02/CatchingCatching
/Team02/ThrowingThrowing
/Team03/ 
/Team03/CatchingCatching
/Team04/ 
/Team05/ 
/Team05/Sprinting


I want to produce a pie chart that shows three simple segments: the proportion of rows that had the highest count; the proportion of rows that had the lowest count (which will always be 1) and the other.

So here:
highest category would be 5 (as it's counted all the blanks)
lowest would be 2 as 2 "Page-type"s have only 1 entry (Running and Sprinting)

other category would be 5

 

In Excel I would just nest a MAX/MIN functions into COUNTIF functions nested into some IF statements, but can't figure it out here

 

Anyone got any ideas?

 

Thank you

 

////////////////////////F U R T H E R   E L A B O R A T I O N//////////////////
[I guess there is a table inbetween these two things like this

TypeCount
[Blank]5
Catching3
Throwing2
Running1
Sprinting1

I got a measure that did this using: 
CALCULATE(COUNTA('Users and average'[Page Type]),FILTER('Users and average','Users and average'[Page Type]=[Page Type]))

 

 


But I feel no closer to getting to the end point.]

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi @Coriel-11 ,

 

If I didnt understand wrong,create 2 columns as below:

_count = CALCULATE(COUNT('Table'[Page-Type]),FILTER('Table','Table'[Page-Type]=EARLIER('Table'[Page-Type])))
Column =
VAR _highcount =
    CALCULATE ( MAX ( 'Table'[_count] ), ALL ( 'Table' ) )
VAR _lowcounnt =
    CALCULATE ( MIN ( 'Table'[_count] ), ALL ( 'Table' ) )
RETURN
    IF (
        'Table'[_count] = _highcount,
        "highest count",
        IF ( 'Table'[_count] = _lowcounnt, "lowest count", "the other" )
    )

And you will see:

vkellymsft_1-1635837036790.png

For the related .pbxi file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

View solution in original post

7 REPLIES 7
v-kelly-msft
Community Support
Community Support

Hi @Coriel-11 ,

 

If I didnt understand wrong,create 2 columns as below:

_count = CALCULATE(COUNT('Table'[Page-Type]),FILTER('Table','Table'[Page-Type]=EARLIER('Table'[Page-Type])))
Column =
VAR _highcount =
    CALCULATE ( MAX ( 'Table'[_count] ), ALL ( 'Table' ) )
VAR _lowcounnt =
    CALCULATE ( MIN ( 'Table'[_count] ), ALL ( 'Table' ) )
RETURN
    IF (
        'Table'[_count] = _highcount,
        "highest count",
        IF ( 'Table'[_count] = _lowcounnt, "lowest count", "the other" )
    )

And you will see:

vkellymsft_1-1635837036790.png

For the related .pbxi file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

Thanks @v-kelly-msft. That's really helpful. Originally I was trying to get it as a measure so if the data was accumulating similarly month of month the count would work more dynamically, but I've now realised that you can't use measures as a legend, so I think this is the best solution.

 

Plus I learned about "EARLIER" which is really useful, so I really appreciate that.

 

Many thanks,

 

Matt

Hi  @Coriel-11 ,

 

Glad to help.

Yes,measure is not suitable in this case.

If your issue is solved,could you pls mark the reply as answered to let more people find it?

Thanks in advance.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

 

 

Sorry, yes! Was about to and then someone started messaging me!

Thanks again.

 

Matt

🙂

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

amitchandak
Super User
Super User

@Coriel-11 , I think, this should be like

CALCULATE(COUNTA('Users and average'[Page Type]),FILTER(allselected('Users and average'),'Users and average'[Page Type]= max([Page Type])))

Hi @amitchandak, thanks for taking the time to help, it's made me realise I hadn't laid my post out very well - The bit at the bottom was really just me trying to explain what I'd tried so I've edited my OP to hopefully make it a bit clearer.

 

What I'm really trying to do is get the pie chart with the 3 slices,

a. one that is the blank category (which will always have the highest value for 'count';

b. one which is anything that has a count of 1, i.e. the MIN; and 

c. one that gathers together everything else

 

Hope that's a bit clearer. Apologies for the poor description

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.