Power BI Insights: Cross joins; Storing data; Dyslexia-friendly formatting; Switch measures

September 6 2020

Power BI pros share their tips on cross joins in Power Query, automating longer-term data storage, making reports more usable by people with dyslexia and applying switch measures.

Cross joins in Power Query

Chris Webb explored how to optimize performance with rare cross-join merges. By default, cross joins aren't available as a join kind in Table.Join or Table.NestedJoin M functions, but there are two different ways to create them in Power Query. The first option involves creating a custom column in the query that contains the query targeted for cross-joining. Alternatively, users can create a custom column with a single value and merge the two queries.

To test the two options, Webb created a query called SourceQuery, returning a table with a thousand integers. Through a variety of steps, he created a second column referencing the first query and shared M code to accompany it. Applying timings from Profiler, he tested the performance. Webb found that the second approach, with a custom column containing a single value was more effective and wrote:

There’s another advantage of this second approach too, if you’re using a foldable data source like SQL Server: it is possible to make query folding happen, which is of course incredibly important from a performance point of view, although you have to be careful not to change the data type of your custom column (or at least be careful how you do it).

Setting data to store for two years with Power Query

Gilbert Quevalliers, writing on Four Moo, took a look at how to automate the process of storing data. It can help to have data available for more than two years and this is straightforward to achieve by using filters in Power Query without hardcoding. To get started, he went into Power Query, navigated to a date column, confirmed the date type and then selected date and custom filters.

The Filter Rows window pops up. Clicking on Advanced, Quevalliers set it to filter "in years" configuring for year to date and then applying it to the dataset.  

Making tables geared toward people with dyslexia

Writing on DataVeld, David Eldersveld shared his own experience raising a child with dyslexia and noted that as much as 20 percent of people may have some form of dyslexia. Currently, Power BI is limited to around two dozen font choices, with both serif and sans serif forms, together with bold and light or monospaced and non-monospaced versions.

Considering the number of people with dyslexia, Eldersveld recommends avoiding italics, increasing font size, using sans serif fonts and upping contrast between characters. To achieve this in Power BI reports, users may want to adopt common fonts like Arial, Verdana, Trebuchet, Tahoma, Calibri or Comic Sans.

Dynamically formatting switch measures

Microsoft MVP Matt Allington, writing on the Excelerator BI blog, discussed the role of switch measures. Today, these are a common way to toggle results so that users can switch what data they are visualizing. He wrote:

This technique uses a disconnected table, a slicer to receive the user selection, and a switch measure to change the result of the measure based on user input. When you select an option on the slicer you can have your chart update to show the result you want to see in the chart…While this technique is useful, there has been a limitation with this approach; the final measure can only have a single number format.

Back in 2016, he received 3000 votes on the Power BI ideas site for recommending that Microsoft allow formatting based on a selection in a switch measure—something that's now possible with Calculation Groups rather than switch measures. Allington demoed how to set up a report based on the AdventureWorks database, adding a chart and splicer and going through the process of creating a Calculation Group, ultimately dynamically filtering the chart.

FREE Membership Required to View Full Content:

Become a MemberLogin
Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more, and it’s all FREE. You’ll also have the option to receive periodic email newsletters with the latest relevant articles and content updates. Learn more about us here
About MSDW Reporter

More about MSDW Reporter