Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 Stem | Page-Type |
/Team01/ | |
/Team01/Catching | Catching |
/Team01/Throwing | Throwing |
/Team01/Running | Running |
/Team02/ | |
/Team02/Catching | Catching |
/Team02/Throwing | Throwing |
/Team03/ | |
/Team03/Catching | Catching |
/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
Type | Count |
[Blank] | 5 |
Catching | 3 |
Throwing | 2 |
Running | 1 |
Sprinting | 1 |
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.]
Solved! Go to Solution.
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:
For the related .pbxi file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!
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:
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!
@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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |