My Power Platform Summit Europe Recap

Last week the Power Platform Summit Europe was held in Amsterdam. I was there together with my colleagues Sander and Pim and next to the learning experience the conference gave me, it was really nice to join them!

Before starting my recap I’d like to point you to the following tool: Microsoft Office Lens, it’s available both on iOS and Android! In every session I was in there were always people taking pictures of the slides. I sometimes do it also, just because I want to make a note next to the slide or because it’s something in a demo I’d like to capture. But the point I’m trying to make here: do it with the Office Lens app, because then your pictures will look like this:

Pro tip for making pictures of the screen: please use the Microsoft Office Lens app, available on both iOS and Android! :-) #PowerPlatformSummit pic.twitter.com/zRvWwL3rNp

— Nicky van Vroenhoven (@NickyvV) 27 maart 2019

The information/sessions that stood out the most for me:

Line Krogh talked about Brons, Silver and Gold Power BI report templates:

• Gold: created by BI team on a managed Tabular model
• Silver: not created by BI team on a managed Tabular model
• Brons: not created by BI team on non-certified data sources

The Art of Gathering Effective BI requirements by Mico Yuk

• An interactive session where she also shared her spreadsheet to be used in discussion with end-users

Nikhil Gakwaed showed us some nice improvements in the Power BI Service:

• Comments on dashboards
• Organizational branding for Power BI Home

Callum Green did a session where one part about data profiling was interesting in particular:

• Data profiling in M (Table.profile)
• He also did some tweaking afterwards to get the profiling on the column and value level

The Value of Self-serve BI in a Large Organisation by Rishi Sapra:

• What-if scenario builder, which he will explain in-depth at the virtual Power BI Days

Demo of splitting the report and the data model by Just Blindbaek:

• This will be even better when Shared and Certified datasets are available, hopefully later this month!

Power BI Performance by Kasper de Jonge, David Magar and Bhavik Merchant:

• Avoid Bi-Di crossfiltering: you will go to hell! (according to Alberto Ferrari)
• Instead turn crossfiltering on in DAX: CROSSFILTER(Dim, Fact, OneWay/Both)

• Use variables, especially with IF
• Don't use IFERROR
• Use DIVIDE
• Model Star-schema/Snowflake
• Filter on Dimensions

A great session on Aggregation Tables by Reza Rad:

• Import mode, Direct Query and Dual
• Use DaxStudio to analyse the queries send by DQ

At the end of day 2 we had a nice view of the entrance to the conference:

Nice ending of a great 2nd day at the #PowerPlatformSummit! So many good sessions today! Good meeting so many friends again and also making new ones who share the same love for #PowerBI pic.twitter.com/pzvLZPypcc

— Nicky van Vroenhoven (@NickyvV) 28 maart 2019

In the morning I found some time to go running with Sander in the Amstel Parc just across the road from our hotel. Although it was very chilly, it was worth the early wake up!

Just came back from a 6K run with @SLKerstens, getting ready for the last day of #PowerPlatformSummit! pic.twitter.com/wWkkKr5zZz

— Nicky van Vroenhoven (@NickyvV) 29 maart 2019

Let me know if you agree with me or if you think you liked other sessions more at the Summit.

My next post will be a recap of the Power BI User Day 2019 in Utrecht where I also gave a presentation together with Jeroen Schalken.

@NickyvV

19th Power BI UG Netherlands (November 2018)

I’ve been meaning to write this blog post for some time now, but I never got to finish it. A little bit late, but here it is!


On November 29, the 19th Power BI User Group was held at our office in Den Bosch. Yes, that’s where the delicious “Bossche bollen” come from. This UG was officially called the Dutch Data Dude farewell tour, because @JeroenterHeerdt has moved to America to join Microsoft in Seattle, as you can read here.

After some delicious sandwiches, snacks and “worstenbroodjes”, Jeroen Schalken and I kicked off the evening with an introduction to data management and the introduction of Power BI at Van Lanschot Kempen.


Michiel Rozema and Henk Vlootman (Quanto) explained how to make financial reports with Power BI. A lot of organizations are used to financial reports in Excel. Readable for the accountant, but less accessible for management. How do you use the strengths of visualization of Power BI to create strategic as well as operational information?

Jeroen ter Heerdt closed the evening and took us on a ride through his world of data visualizations: from pie charts (do NOT use them) to the three roles he thinks are needed for data management:

• Data Engineer
• Data Scientist
• Data Artist.

Making a good data visualization can be difficult. Jeroen showed us the do’s and don’ts to make even better visualizations.

We had a wonderful evening with some great conversations amongst the attendees. Thanks @PBIG_NL for the chance to host this evening!

@NickyvV

My Data & BI Summit Recap

I finally came around to writing a wrap-up for the Data & BI Summit in Dublin last month.
No fancy pictures or colors, just a to-the-point summary.

New features

• Incremental refresh – Premium (included in May update as preview feature)
• Drillthrough with current filters and parameters (included in May update)
• Common Data Service For Analytics (preview live)
• Theme file additions coming soon:
• Theme export
• Background added to themes
• Table/matrix headers
• Persistent filters (per user) in the service
• Buttons to trigger action (included in May update)
• Q&A and linguistics
• App workspaces without Office365 group

Modelling

• Optimize your model (size)
• Make use of Query folding in Power Query
• Applies to Import mode only
• Filter first, Transform later
• Use supported transformations first, non-folding steps at the end
• Writing a SQL query in Get Data prevents folding
• Create a blank query with #shared to show a document library of all M functions (Blog)

Visualization

• Choose a theme
• Report theme generator at PowerBI.tips
• Color Theme Creator at Aka.ms/pbitheme
• Outlining
• Canva.com
• PowerPoint
• PowerBI.tips
• Storytelling, with:
• Custom visuals (e.g. Timeline Storyteller)
• Bookmarks
• Tooltip pages
• PowerApps visual
• Drillthrough
• Syncing slicers

Governance

• Use App Workspaces for DTAP
• DEV_Finance
• UAT_Finance
• Finance
• Use Onedrive for Business for versioning your pbix-files, with the same folders as the App Workspace
• Use the API’s with PowerShell to copy resources between workspaces
• Chriss Webb (@cwebb_bi) wrote a successful script in response to a video from Adam Saxton (@GuyInACube)

Best practices and notes

Visualization | Model

• Driving table for measures
• Hide unused columns
• Totals in matrix and tables insensitive to row filters
• Uniform naming of measures and calculations
• Sync slicers
• Add descriptions where possible
• Make sure filters are not hardcoded

Governance

• Add an about page with last refresh date time and description of Model
• Version the Model
• Schedule datasets using Admin account for one place to monitor failure notification emails
• Use one drive as source control
• Golden dataset

Further reading

• Power BI Whitepapers
• PowerBI.tips
• Power BI User Voice

@NickyvV

Suggested reading

I came across some nice articles and blog posts this week I wanted to share:

• The (unintended) network effect of Excel by @PowerPivotPro
• Junior DBA interview questions by @SQLRockstar
• Comments and descriptions in DAX by @TechniTrain
• How far can you push tabular? by @MarcoRus
• The emergence of Silicon Valley (documentary) by @AlexanderNL (+Alexander Klöpping)

Hopefully you can get somehting useful (or funny) out of the articles!

You can also follow me on Twitter: @NickyvV

CALCULATE - The Queen of all DAX functions: From Row context to Filter context

This post is an update on AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE). With special thanks to Marco and Alberto explaining the topic of Evaluation Contexts so well! :)

A little while ago I wrote about the different outcome of 2 measures, when trying to find a solution for a problem. Back then I didn't know why they had a different outcome. Since I have been attending the SSAS BISM Tabular workshop by Marco and Alberto this week, I couldn't resist to look at the problem again.

Let's start with the 2 measures:

(1)
AVERAGEX(
        FILTER(
              Organisatie,
              Organisatie[Type] = "Woonlocatie"
        ),
        SUM(Productie_OrganisatieGroei[AantalClienten])
)

(2) 

SUMX(

    Productie_OrganisatieGroei,

    CALCULATE(

             AVERAGE( Productie_OrganisatieGroei[AantalClienten]),               

             Organisatie[Type] = "Woonlocatie"

    )

)

To understand the relationships between the tables, there is a 1-to-Many relation between Organisatie and Productie_OrganisatieGroei:

Datamodel

Starting with measure 1, AVERAGEX is an iterator, so it creates a Row context on Productie_OrganisatieGroei. The FILTER on Organisatie however, because it is in a Row context, is not propagated to the many-side. So calculating the SUM of [AantalClienten] doesn't take the filter into account and takes the sum of the whole Organisatie table.

Measure 2 also starts with an iterator (SUMX) which creates a Row context, but then uses CALCULATE inside, which creates a Filter context on that one row. Remember that Filter context does propagate over relationships. Now that we have created a Filter context by using CALCULATE, the filter on Organisatie[Type] does propagate through the relationship and filters the Productie_OrganisatieGroei table. That is why this formula does produce a correct answer on the problem I had.

Once more the pivot table which has measure 1 and 2 in it:

Note: although the project was done with PowerPivot v1, I just imported the xlsx-file in SQL Server Data Tools (Visual Studio / former BIDS) to extract the datamodel for my workbook:

SSAS BISM Tabular Workshop

On april 16 and 17 I attended the BISM Tabular workshop from Marco Russo and Alberto Ferrari (from SQLBI.com with my colleague Bas Kroes. We were guests at Microsoft BV @ Schiphol, the facilities were excellent, although the temperature in the room was a bit too high these days.

BISM Tabular Workshop

It were 2 long days, also because on monday evening we attended the PASS Community event, co-hosted by ADA ICT, where Marco and Alberto talked about: Tabular or Multidimensional?

PASS Community event

I've been working with PowerPivot for Excel for almost a year now, but haven't done very much on SSAS, and nothing with Tabular (in regards to customers) yet, so I learned a lot in the workshop.

Specially the filter and row context in DAX was very good explained by them, it's still a hard topic, especially with 1-to-many and many-to-many relationships.
I can recommend the workshop to everyone who wants to know more on Tabular and SSAS 2012!

Learn DAX Basics in 30 minutes!

Now is the time to start with DAX: a few days ago a tutorial has been released to help you learn DAX basics quickly.
The QuickStart: Learn DAX Basics in 30 Minutes is available as part of the MS Technet Wiki, so we can all contribute to it also!

This QuickStart is for users new to PowerPivot or tabular model projects authored in SQL Server Data Tools. It is meant to give you a quick and easy introduction on how you can use Data Analysis Expressions (DAX) to solve a number of basic data modeling and analytical problems.