Power BI Accelerator Week #3 is Live!

Power BI
62

@Keith , @The_Bishop ,

It’s just been posted here:

But when I’m clicking on it, I’m getting a strange encoding message. Let me check it out with the @EnterpriseDNA team and get back to you shortly.

  • Brian
2 Likes
63

thanks @BrianJ

1 Like
64

Video seems to be running fine now.

  • Brian
1 Like
65

its working fine now for me too :slight_smile:

1 Like
66

Power BI Accelerator Participants,

Wow, action-packed end to a great Week #3 Accelerator cycle. Continued terrific participation, and a great job by everyone on quite a difficult problem set this week.

We concluded the cycle last night first with an in-depth lesson on changing filter context using CALCULATE (the key to advancing beyond simple aggregate measures and unlocking the full power of DAX) that hopefully provided everyone some useful insights into how DAX alters and applies filters within the data model. We specifically focused on the five key members of the CALCULATE Gang – ALL, REMOVEFILTERS, ALLSELECTED, ALLEXCEPT, and VALUES and how to use them effectively.

Then we ran through the Week #3 solution in detail, and answered a series of member questions. If you missed the event, we have a full video of it at the address below along with the original problem, copies of the slides and Sam’s and my solution files.

The Week #4 problem set will be coming out next Wednesday, August 18. In the meantime, it’s a great opportunity to take the skills you’ve learned in Accelerator and apply them in the Enterprise DNA Data Challenge #15. :grinning:

Thanks for participating! And as always, we welcome your feedback on the Accelerator initiative.

– Brian

1 Like
67

Brian,

Thanks for the great session on Wednesday. I have a quick followup question on a DAX pattern that has been in almost every EDNA training video.

In the DAX below, why use the MAX function? If I understand, the field to the right of the operator is the current value (row in the visual) that is evaluated against the field to the left of the operator. In my head it is the max value by default.

Is the MAX function required to designate a singular value to evaluate against?

Thanks for all the help!
Brad B

Cumulative Profits =

CALCULATE(
[Total Profit],
FILTER(
ALLSELECTED( Dates ),
Dates[Date] <= MAX( Dates[Date]
)
)
)

2 Likes
68

@bboehnke,

Thanks! Glad to hear you liked the session last night. Had a great time prepping for that, and @sam.mckay and I really enjoy doing them.

In answer to your question - BINGO! That’s exactly what the MAX function is doing. Every filter condition has to evaluate to true or false (for DAX to know whether to keep that row in the filter - remember: filters are just virtual tables), and to do so, you can’t have a “naked column” on the right side of the condition.

Here’s the way I like to rewrite the standard cumulative pattern to make it more intuitive to me (and hopefully the report user as well):

Cumul Profit ALLSELECTED = 
VAR CurrentRow = MAX( Dates[Date] ) 
VAR Result = 
CALCULATE(
    [Total Profit],
    FILTER(
        ALLSELECTED( Dates ),
        Dates[Date] <= CurrentRow )
    )
) 

RETURN
Result

FILTER is iterating, and that CurrentRow is stepping down one date/row each iteration.

Is that helpful?

  • Brian
69

Thanks @BrianJ . I went to bed and it hadn’t then been posted; I woke up and it had! I was able to finish off my viewing.

1 Like
70

This helps. I think it is starting to click. Like you and Sam tell us, practice is key.

Just to confirm. I assume the variable should be down in the CALCULATE function.

Cumul Profit ALLSELECTED =
VAR CurrentRow = MAX( Dates[Date] )
VAR Result =
CALCULATE(
[Total Profit],
FILTER(
ALLSELECTED( Dates ),
Dates[Date] <= CurrentRow )
)
)

RETURN
Result

Thanks,
Brad B

71

@bboehnke ,

Aaargh, my bad. I had a typo in my previous measure - declared the CurrentRow variable, then forgot to change it in the FILTER statement, which I’m sure was super confusing. However, you got it exactly right in your statement of the measure. I’ve since gone back and corrected the typo, so yes - your understanding is spot on.

We’ll go deep dive on variables in Accelerator #4, but the key is that PBI treats variables as constants, so you’re exactly right that to get them to increment properly here, they need to be in the iterator (in this case FILTER) portion of the measure.

Again, apologize for the confusion.

  • Brian
72

@BrianJ

No problem. I appreciate the help.

Thanks,
Brad B