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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.