Power BI Accelerator Week #3 is Live!

[Eze]

Many thanks @BrianJ

[stevens]

Thanks Brian. Shout out to @jamie.bryan for the calculation / methodology of highest and lowest State. Went round in circles with min, max and if. Withdrew the model to try again. Still no luck!

Ended up practicing the use of VAR to reduce one off measures.

Great challenge and learnt a huge amount. Thanks to everyone who helped set it up.

EDNA Power BI Accelerator – Week 3 Problem Final.pbix (14.3 MB)
hanks

[BrianJ]

@stevens

Ended up practicing the use of VAR to reduce one off measures.

LOL. Yeah, in retrospect, I probably should have covered variables first since I’d forgotten how many extra measures you have to write if you don’t use them…

Really glad to hear that you found the exercise so valuable. Thanks for the feedback!

• Brian

[JohnG]

Hello Brian:

Regarding the map visual:

1. Should each Region be treated as a separate entity; that is, a top and bottom for each selected Region?

Or…

2. If multiple Regions are selected, should the selected regions be treated as one larger region with only one top and one bottom?

Thanks.

John Giles

[BrianJ]

John,

Good question. I envisioned it as #2, since that provides the user with more information. Otherwise, to get the min/max for each region, they could just make sequential selections in the slicer.

• Brian

[JohnG]

Thanks, Brian:

Prompt as always.

John Giles

[Keith]

Hi Everyone,

To be honest still trying to get my head around this calculate function but watching the videos , seeing examples of calculate format does help. Copying the format and just applying the naming conventions that i use in my model. Just hoping put the right information in it.

Please see attached file. Any comments is welcome.

See you next week for the live solution.

@BrianJ Did you post the Registration link yet?

Thanks
Keith

EDNA Power BI Accelerator – Week 3 Problem Keith Smith.pbix (9.7 MB)

[BrianJ]

@keith,

Well done! Looking through your report - it all looks spot on.

I honestly believe 30% of people’s struggle with CALCULATE is the fact that it is the most poorly named function in DAX. Only in the most abstract sense does it really have to do with calculations. If I could snap my fingers and change the name to CHANGECONTEXT(), I believe DAX would immediately make more sense to the majority of users.

Seems that you’re well on the road to a good understanding, and hopefully the live session will help shed some additional light.

Per your question, the registration info is now up on this post:

Registration Information for Power BI Accelerator Week #3 Live Solution Event

Members, We hope you’re enjoying the Week #3 exercise for Power BI Accelerator. It’s definitely a bit more challenging than the first two, but we think the payoff is immense, because the concepts it addresses are the foundation of all DAX measures beyond just the basic aggregation measures we covered in Week #1. @sam.mckay and I are excited about the solution event for week #3, and are preparing both a detailed lesson on the key concepts, as well as a specific walkthrough of the solutions to …

Thanks for participating!

• Brian

P.S. Happy Cake Day.

[Keith]

Hi @BrianJ
Thank you so much for the birthday wishes. I had a great day.

I just saw the registration come up and have register for the session. I hope you receive the information.

I appreciation your input.

I’m looking forward to the session.

thanks again
Keith

[JohnG]

Hello Brian:

Here is my submission for Accelerator #3.

Before going any further, I will state that I wouldn’t have completed the task if it weren’t for Stevens and Jamie.Bryan and their work on the Map Visual (see below).

This Accelerator was a challenge, certainly.

I figured out the Cumulative, Mode and Average Visuals, quickly enough, unless you tell me something differently. The Map was a real struggle.

I went through MINX and MAXX and RANKX. At one point, I thought I had the answer with RANKX. Everything looked fine on the tables I used to test. However, it all fell apart on the Shape Map and the Diverging colors.

I worked with TOPN, but couldn’t figure out a way to return the Top and Bottom states until I referred to Steven’s and Jamie’s solutions with the +1, -1 for the Top and Bottom.

I thought I’d add a Tooltip to the Map for the State Ranking within ALL states and the State Ranking within ALLSELECTED states. I thought it would be easy, the ranking within all states is fine, the ranking with ALLSELECTED states, doesn’t work. More to be learned regarding context.

Regards,

John Giles
EDNA Power BI Accelerator – Week 3 SAVE.pbix (9.7 MB)

[BrianJ]

@JohnG ,

“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.

• Brian

[JohnG]

Thanks, Brian:

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.

John Giles

[BrianJ]

John,

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:

The definitive guide to ALLSELECTED - SQLBI

• Brian

[bboehnke]

Brian,

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.

EDNA Power BI Accelerator – Week 3 Problem Brad Boehnke.pbix (9.7 MB)

Thanks,

Brad Boehnke

[stevens]

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.

[BrianJ]

@stevens ,

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:

Set a Minimum Sample Size of n Number to Show Visual

@iasma, Totally doable in Power BI, using a couple of cool tricks to control transparency of visuals via DAX and conditional formatting. Here’s a basic mockup of your scenario that I created: [FEBF7E96-8794-4F02-A291-E54854A2517E] The trick here is to create your visuals as normal, and then create a card that says “Insufficient Sample Size” or something to that effect. You can then use the transparency attribute (00) of hex codes to make the background and font of the overlaying card transp…

• Brian

[stevens]

Very cool, will look to use it.

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” ))

[JohnG]

Hello Brian:

I haven’t received the registration for this week’s session to review Accelerator #3.

John Giles

[BrianJ]

@JohnG ,

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.

– Brian

[JohnG]

Thanks, Brian.