Our Top Picks, Insights and Key Learnings from the Power BI Summit 2023 – Part Two
Author: Rachael Potts
The 2023 Power BI Summit, hosted by RADACAD, was packed with presentations by well-known and experienced presenters, including expose’s own Heidi Hasting who, held a session on Power BI Report Server – Things to know.
With so much content available during the virtual conference week, it was tricky picking which sessions to attend. Fortunately, the content remains available to attendees for life, so I still have many sessions on my to-view list in the weeks to come, and I am sure I will come back to some of the content from time to time when I want a refresh.
Below are some of my favourite sessions that I’d like to share with you:
- Advanced Data Modelling with Power BI – by Marc Lelijveld
- Field Parameters and Calculation Groups – Better Together – Parv Chana
- Building a “Forecasted” Actuals Statement – Ken Puls
- Get your Microsoft Certification for Power BI – Cecilia Brusatori
Advanced Data Modelling with Power BI – by Marc Lelijveld
This advanced modelling session followed Marc’s first presentation at the Summit, Data modelling 101: Understanding the basics of a good data model. The advanced session was for those who had attended the basics session or those with more advanced knowledge of data modelling. Three main topics were covered, relationship direction, row-level security and aggregations.
Relationship direction for both singular and bidirectional relationships was described in detail. The default relationship in Power BI for the optimal 1 to Many relationships is that the 1 side acts as the dimension table and filters in the direction of the many table, which acts as the fact table. For the less optimal Many to Many and 1 to 1 relationships, these default to bidirectional in Power BI. Many to Many relationships can be amended by the Power BI Data Modeller to a single direction, and 1 to Many relationships can be changed to bidirectional relationships in the Manage Relationships menu.
The Data Modeller is responsible for deciding on and implementing the most appropriate relationship behaviour needed to facilitate the user requirements and purpose of the model. One of the fundamental principles of Power BI Modelling is to avoid bidirectional relationships where possible. These can have performance impacts, over-filtering impacts and lead to ambiguity in the model. The use of the CROSSFILTER argument in a DAX could be used in some cases to negate the need to create a bidirectional relationship.
The potential impacts of bidirectional relationships to the model were described in detail using an example below:
The light blue pathway shows the default filter behaviour through the data island. The Unit Cost Amount from the Product Inventory table has two filters applied when the Date table is used to filter by fiscal year.
The dark blue pathway shows the direction of the filter traversing the Date – Internet Sales – Product and Product Inventory tables. As each table is filtered, this impacts the results returned in the table visual, leading to over-filtering and ambiguity.
Row Level Security
The direction of the relationship is an important consideration for implementing Row Level Security (RLS) across a model. In many cases, it is only required for the fact table(s,) but where RLS is required to be applied to the dimension tables it was important to note the direction.
Another important consideration was that RLS is not implemented through tables with bidirectional relationships by default. The user must select the “Apply security filter in both directions” check box when setting up the relationship. In some cases, applying RLS to each table individually may be required.
Implementing Aggregations
The benefit of user-defined aggregations in a Power BI model is to improve query performance over large datasets with a DirectQuery connection. Whilst the preferred method of aggregation was at the source level, if this isn’t possible, it can be implemented using Power Query. Marc conducted some aggregations using a duplicate copy of a fact table and applying the GroupBy transformation in Power Query.
The performance benefit comes from the Aggregated table being implemented as an Import storage table and stored in-memory, however the higher-level granularity of the data is still available through the Direct Query mode.
A requirement of Aggregations are regular relationships which are possible with the following relationship combinations:
The opposite of a regular relationship is a limited (AKA weak) relationship. This can occur when there is no guaranteed “1” side of the relationship.
There was a lot of useful information in the session, with much detail about how the model could be impacted by various relationship implementations. I recommend it to those who wish to understand more about relationships and want to improve their modelling skills.
Field Parameters and Calculation Groups – Better Together – Parv Chana
I attended this session as I have recently implemented a solution for a client that combined the relatively new feature Field Parameters with Calculation Groups. I found the solution worked great and was keen to hear more about the topic.
This session covered the use cases for Calculation Groups and Field Parameters, as well as providing use cases for combining the two methods.
|
Uses of Calculation Groups |
Uses of Field Parameters |
|
Reusable calculations |
Dynamic measures |
|
Changing active relationships |
Dynamic columns/dimension |
|
Dynamic format string |
Dynamic slicer |
|
Dynamic measures |
|
Benefits of using Field Parameters over Calculation Groups where possible include situations where your organisation does not wish to use a third-party application and that Field Parameters are easier to maintain within the Power BI Desktop application.
The two use cases provided by Parv for combining Field Parameters and Calculation Groups included:
- when it is necessary to exclude measures, and
- dynamically formatting measures
I was pleased to see the method I applied in the use case for combing a toggle between different measures and dynamically formatting the measures. In my case, I was required to switch between displaying the values in the measures with the full dollar value and rounding up to the thousands.
I do love Field Parameters and Calculation Groups as they add a lot of flexibility to report design. This presentation provided some good examples to users of how to implement, when to implement and how to combine both Field Parameters and Calculation Groups.
Building a “Forecasted” Actuals Statement – Ken Puls
In most organisations, Finance and Accounting reports are a natural use case for using Power BI. This presentation caught my attention as I am doing similar work for a client.
Ken Puls is an Accountant who likes to build reports in Power BI that can easily transition between both Power BI and Excel. He took the audience through his method for creating an Annual Forecast report which provided actual results at the given period and the budget for the fiscal year thereafter. The purpose was to forecast the results at the end of the year, given the actual performance to date.
He explained the challenge of reporting this information visually and the method he uses to design and debug his DAX. We can see in the Power BI matrix below that there are actual and budget amounts for each month.
In the report, the user only needs the actual amount until the selected period and the budget only until the end of the fiscal year. This presents several challenges to implementing in Power BI, and Ken took the audience through his method and process for solving this problem, but also sharing these skills so that the audience can apply the method to other challenges.
The challenges of this task were:
- Using a dynamic cut-off date
- To show the actuals until March (the current month) and then the budget only
- Cross total is the aggregate of the Actual totals from Jan – Mar and Budgets to Apr = Dec
- Be transferable to Excel
The report was also required to have a year and month slicer and an Account hierarchy.
The data model had two fact tables, the budget, which had a monthly granularity and actuals at a daily granularity, which were both connected to common dimensions.
Once the challenge was established, Ken took the audience through his process of developing a solution.
A few of his top tips included:
- Duplicating the report page so that changes could be made during development without causing any issues like accidentally moving a visual out of alignment.
- Building the measure in stages and incrementally testing the results in the report.
- Using text instead of the measure calculation to test the logic in the report view.
In the example below, you can see this method being used to check that the correct value was being returned in each cell within the table visual.
The logic being tested in this case was:
- Select a month
- Return all actuals in the selected year until the selected month <= selected month,
- If month >= selected month, return the budget
The final measure to achieve the desired output is shown below. A switch statement was used in addition to a disconnected calculated table called “EAMonths” containing the Fiscal month name and number to avoid interacting with the report filter which was based on the Date Dimension table.
Expected $ =
VAR vFiscalMonth = SELECTEDVALUE('Calendar'[Month],13)
VAR vSelectedMonth = MAX(EAMonths[Fiscal Month])
VAR vFiscalYear = MAX('Calendar'[Year])
VAR vResult = SWITCH(
TRUE,
vFiscalMonth = 13,
CALCULATE(
[Actual $],
ALL('Calendar'),
'Calendar'[Month] <= vSelectedMonth,
'Calendar'[Year] = vFiscalYear
)
+CALCULATE(
[Budget $],
ALL('Calendar'),
'Calendar'[Month] > vSelectedMonth,
'Calendar'[Year] = vFiscalYear
),
vFiscalMonth <= vSelectedMonth,
[Actual $],
[Budget $]
)
RETURN vResult
I really enjoyed getting a look at the process that Ken used to solve the challenge. I really liked the tip on using text to check measure logic in a report view – I’ll be using that one in future!
Get your Microsoft Certification for Power BI – Cecilia Brusatori
This session was a must for me – I am working towards finally taking the PL-300 exam to get certified. Cecilia provided the audience with advice on getting the Microsoft Certified: Power BI Data Analyst Associate Badge. It also reinforced that I am taking all the right steps in my preparation and gave some insight into what to expect during the exam.
The reasons provided on why Power BI users should gain certification included to:
- Measure your Power BI knowledge
- Learn something new
- Standout from other applicants when applying for jobs
- Boost your career
- Boost your professional profile
- Have fun!
Before the Exam:
The audience was provided with some high-level information which can also be found on the Microsoft Learn website, while Cecilia highlighted the details to keep an eye on. For example, the exam content changes regularly to keep up with developments. The Microsoft Learn webpage contains important information about the exam and provides alerts when new content is added:
Another top tip was to join or form a study group. I can personally vouch for this as I have a study buddy and we meet weekly to go through test questions and talk about exam content. It really helps to stay accountable, talk through why the answers are correct and highlight areas of focus.
Cecilia provided a sandbox resource for a demo exam – https://aka.ms/examdemo – I will definitely be taking this as practice before I take the real exam!
During the Exam:
The exam requires a passing score of 700 (70%). The exam is held either at a test centre or online. If the exam is taken online, the participant is required to be alone and keep their camera on the entire time.
Types of questions included in the exam are:
- Multiple choice
- Case studies
- Drag and drop
- Build list reorder
- Matching questions
- Active screens
- Labs
After the Exam:
Once the exam is taken and Certification has been awarded, this expires every year. Participants are required to take an assessment to renew. This is free and easier than the initial exam.
Her closing advice to taking the exam included:
- Don’t wait until you feel 100 per cent prepared
- Keep track of time you have left during the exam
- It is ok to fail, especially if it’s your first attempt
- Read the question first and the information given after
- Go to the community for help and support whilst preparing
- The exam is not what you know – it is what Microsoft wants you to know
I’m certainly inspired and looking forward to getting certified very soon!
Want to hear more about the Power BI Summit?