“I figured out the Cumulative, Mode and Average Visuals, quickly enough, unless you tell me something differently. The Map was a real struggle.”
Nope, you nailed all of those spot on. Well done!
A word to all about the map visual. That one is giving a lot of folks fits. It’s actually a bit of an unfair question because it requires two things we haven’t discussed yet (iterating functions and conditional formatting), and I semi-regret putting it in there. The challenge with Accelerator is that we have folks with a pretty wide range of experience and skills participating (which is awesome!). I included the map exercise to give participants at the high end something to chew on if they blew through the first three parts quickly.
However, if you’ve successfully completed the other three, then you’ve achieved what we hoped you would have pertinent to the primary concepts we wanted to focus on this week. If you still have time and motivation to wrestle with the min/max profits map exercise, by all means go for it but definitely treat it as an optional “extra credit” exercise.
I followed Sam’s advice and tested all my measures via Tables before applying them to their respective visual. Good advice, but still a no go with the Tooltips which I thought might take 5 minutes. Still with much to learn with Context.
We’ll dive deep into ranking in Accelerator #4, since that’s on of the most commonly used and important iterating (“X”) functions. Row context and iterators are going to be a focus of #4.
Note: if you search for posts on the forum using RANKX and ALLSELECTED, you’ll see that that combo causes a LOT of problems. ALLSELECTED actually has some very subtle and deep complexities associated with it. Even Russo and Ferrari have stated that in the past there were some things about that functon that even they didn’t fully understand, and they have said that by a wide margin it is the most difficult function in all of DAX to completely compehend. Unfortunately, unlike many other DAX functions that you can sidestep by just using different functions, this one has no complete substitute - we’re all stuck with it.
I’ve used this analogy before, but ALLSELECTED is like the volatile, unstable crew member in every heist movie (think Jeremy Renner in The Town…). Necessary, because they possess a certain unique skillset, but also the one most likely to get all the other crewmembers killed…
If you’re inclined to take a deep and difficult trip down the ALLSELECTED rabbit hole, here you go:
Hello. Here is my submission for the Accelerator #3.
Yet another great challenge. I have plenty of DAX work ahead of me. I reviewed other submissions to get me past the finish line. Looking forward to the solution review.
Also had a bit of fun using the following format measure on the top left graph.
+ive/-ive Formating =
SWITCH( TRUE(),
[Above or Below Average Profit] >=3, “#229f71”,
[Above or Below Average Profit] >=-30, BLANK(),
[Above or Below Average Profit] <100, “#f73700” )
Cannot remember whose youtube clip it was on but it has been really useful to someone who does not like playing with the conditional formatting colour bar.
In the conditional formatting menu select FORMAT BY - Field Value and then select the measure.
Its worth noting that BLANK uses the original colour selection so make sure that is selected in the graphic before connecting the measure.
In the measure above the positive is green and negative red.
I picked up that trick a while back from @JarrettM, and now do 95%+ of my conditional formatting that way.
An additional cool trick you can add to that is controlling the transparency of the color with two more digits added onto the end of the hex code. See this thread for example:
Have you come across this one before? It highlights lowest and highest within a range. This formula is set up for months in year. I find it useful when the data is of a similar size and you need the high lows to stand out. Learnt from one our EDNA members.
Highest Value =
VAR highestvalue = MAXX( ALLSELECTED( Dates[Month & Year], Dates[MonthnYear] ), [Total Sales] )
VAR lowestvalue = MINX( ALLSELECTED( Dates[Month & Year], Dates[MonthnYear] ), [Total Sales] )
return
if ( [Total Sales] = highestvalue, “#229f71”,
if ([Total Sales] = lowestvalue, “#f73700” ))
Thanks for letting me know. We are definitely having some problems with the automatic email responses on the registration system. I will talk with the @EnterpriseDNA team and confirm you are on the registration list, and make sure that the emails with the connection link go out in timely fashion to all members registered.
I was able to learn more about the differences between the ALL and ALLSELECTED functions
Had to create a new calculated column (YearQuarterKey) in the Dates table and use that to sort the YearQuarter field (Not sure if this is the best approach though, any ideas are welcome)
Struggled with the Above, Below Average and Unprofitable computations. Will appreciate any help on this
Thank you all, feedbacks will be greatly appreciated
Excellent effort. Still some learning here to be done, but that’s what this initiative is all about. I think it will make the solution we go over tomorrow much more resonant and meaningful for you given the effort you’ve put in.
