Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I want to consider the average scan rates of each of the 4 employees for a given week and then return the Max and Min values for a week.
I have the following measures: (and many many more that' I've tried!)
For the Max, I tried
WeeklyMax:=CALCULATE(MaxX(VALUES(PagesTime[Iso Week]), [Avg PPH]),all(PagesTime[Employee]))
I even thought I had found the brass ring with the solution explained here and here. But neither seemed to work for me.
I can’t for the life of me figure out how to take the Max/Min of a measure in the context of the week.
Anybody interested in pointing out what I’m missing?
I don't see a means of attaching my file. But hopefully the screen shots will suffice.
Thanks in advance…
Rich P
Solved! Go to Solution.
OK so I had a crack at this and I got it working with the following:
Avg Scan Rate = CALCULATE(AVERAGEX(Data,DIVIDE(Data[Scans],Data[Hours])),ALLEXCEPT(Data,Data[Week],Data[Employee]))
MAXX = CALCULATE(MAXX(ALL(Data[Employee]),[Avg Scan Rate]))
The answer for me was in one of the posts you linked... I think the ALLEXCEPT in the scan rate measure is crucial for evaluating the context correctly.
I haven't completely unpicked this myself so I can't give a proper explanation as to why it works sorry. There is probably a more elegant way to do this but hey worked for me 🙂
I hope this helps!
Your DAX is good, but it looks like you are not thinking about the problem in the terms of filter context. As I understand, you want a line that gives the overall max over all weeks in the chart, right? I suggest you go through the following process.
1. set up your data as a matrix or table (so you can see the numbers). Put the weeks on rows and the measure that varies by week next on rows or columns (depending on if you have a table or matrix respectively)
2. write your formula that is supposed to give you a straight line and add it to the visual.
If the measure is not giving you the same number all the way down the page, ask yourself "why not" while thinking about "filter context". The rows in the visuals will filter your datamodel. Your job is to write a formula that ignores the weeks on rows and gives you the avearge over ALL periods in the visual (that was a hint 🙂 )
Matt - It's a pleasure! Yours was the first book on DAX that I bought!
Actually, I am trying to find the Max & Min in each week. And as you wrote in your chapter 6, I even tried a couple of approaches:
a) using a measure to calculate the Sum of Pages and another to calc the Sum of Scan Hours and another to calc the Average Scans/Hr
b) using a calculated column
But no matter how I try to write the Max measure, It always returns something that is outside the row context of Week Number.
Any suggestions? Or is there any way to attach my file? This is my first post, so I'm not sure if you have to be more active before that feature is unlocked?
Thanks for the reply.
Rich
p.s. Very subtle hint {ALL()} LOL
you can load a file to dropbox or onedrive and post the link. If you want to add back the week filter for the current week, simply add VALUES(Cal[Week]) as an extra filter argument to calculate.
I have tried a simplified model to start from scratch and thought I had it, but it just isn't returning the values I want.
Per your suggestion, I have included a link to the example file. I'm hoping you can take a few minutes to look. Or anyone else, for that matter.
Thanks again.
Rich
OK so I had a crack at this and I got it working with the following:
Avg Scan Rate = CALCULATE(AVERAGEX(Data,DIVIDE(Data[Scans],Data[Hours])),ALLEXCEPT(Data,Data[Week],Data[Employee]))
MAXX = CALCULATE(MAXX(ALL(Data[Employee]),[Avg Scan Rate]))
The answer for me was in one of the posts you linked... I think the ALLEXCEPT in the scan rate measure is crucial for evaluating the context correctly.
I haven't completely unpicked this myself so I can't give a proper explanation as to why it works sorry. There is probably a more elegant way to do this but hey worked for me 🙂
I hope this helps!
DearWatson - THANK YOU!!! This is perfect, Exactly what i was looking for.
Not sure how you came up with that, but it certainly works.
I'll have to go over it a few times to understand what it is doing, but it will be worth it.
Thanks for taking the time to resolve this for me.
Best Regards,
Rich P
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |