Power BI July 2020 Mini Blog – Launch External Tools

This is part of the Semantic modelling for Power BI Programme and its a big one. Very exciting

The first thing you should see is External Tools on your ribbon (Making sure you are on the Jusy 2020 release of Desktop)

In the first instance, this doesn’t appear to be avilable. Why is this missing?

This is because you may not have the latest releases of the External tools.

These tools are:

  • ALM toolkit
  • DAX Studio
  • Tabular Editor

Currently, I only have DAX Studio but this isnt enough to give me the External tools Menu item? Whats going on?

DAX Studio

My Current release is

Going to DAX studio there is a new version

https://daxstudio.org/

this is the release you need for Power BI. Click on the installer to download the new release (Power BI Has been closed down during this process)

Do you want to run this file? Click Run

Once installed open up Power BI

External Tools is now an option

However DAX Studio is greyed out

go into Preview Features and make sure Store datasets using enhanced metadata format is turned on.

Check the External Tools Folder

C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools

I now have the DAX Studio JSON file in the External tools folder

ALM Toolkit

http://alm-toolkit.com/

After installing, take another look at your external template Folder

Tabular Editor

https://tabulareditor.com/

Again, a new JSON file is added to the folder

So now we have all three tools connected into Power BI. Lets have a look at what they can do for you. the following is a few basics from each tool

DAX Studio

With a Power BI pbix file open. go to External tools and click on DAX Studio. It will automatically connect to the data set you have open

Go to Advanced and View metrics

This is a great way of using the Vertipaq Analyser to look at Tables, Columns, relationships, partitions and a Summary of your data model

You get to see which items have low and high cardinality. What items are taking up too much space in your model and really helps you get a feel for what you can do to make your model better

ALM Toolkit

The ALM toolkit comes from the Analysis Services space. Due to Power BI encompassing an Enterprise architecture along with self service, we can now start to use these community tools with Power BI datasets.

Its a schema comparison tool for your datasets.

Use it many scenarios such as incremental Refreshing, metadata only deployment (Now available for Premium and Pro)

This is another big hitter and requires a much more in depth look

Tabular Editor

Again, tabular Editor will connect to the tabular database you have open within your pbix file.

This is an excellent tool to help with quickly modifying tabular models.

If you do anything in here, like add measures into a folder, you will see the change straight away in Power BI Desktop.

You can also test and create measures in here which can offer a much easier way to do this than within power BI Desktop.

basically, using this tool you can automate some of the more tedious tasks and save lots of time.

Like DAX Studio. You can also analyse your entire model to get a feel any improvements you can make.

There are some great videos out there on how to make the most out of this tool and if you use Power BI heavily this is a must have.

Power BI table Visuals, and the STAR Schema. Ensuring you have a connection between your Visuals

Working with users who like a lot of Table visuals in their reports, there is sometimes a tendency to not want to include the measure in the table visual which can result in the following annoying situation

Using another really basic example

the Fact is about Events and the join is between EventtypeKey and DateKey

Our Visual contains the Event Description from the Event Dimension. this visual could contain data items from multiple dimensions but the thing to understand here is that these dimensions are not part of the column chart visual

The Column visual contains Month name from the Date Dimension and the Number of events metric

Number of events = DISTINCTCOUNT('Fact'[EventKey])

If we click on a column the hope is that the table visual interacts and we only see Events in that month.

Not the case. You can click on a bar and you still see every event description. In this instance our join is not helping. How you you resolve this?

Adding the measure into the table visual

The connection is now there because of the measure so this works. But the user doesn’t want the measure in the table visual.

So what Next

Add a Filter to the table visual

If we ensure that we only show items where that specific measure is not blank we can finally see the visuals interact.

A simple filter and a bit of thought over the data model and the joins has resolved the issue and now we can have tables with data from other dimensions that aren’t used in the visuals

Power BI July 2020 Mini Blog – Gradient Legends

Its always nice to work through the updates by relating them to something you are working on.

Lets have a look at the Gradient Legends

lets start with a simple events chart for 2020

the Measure is

Number of Events = DISTINCTCOUNT('Event Metrics'[Event Id])

So Now lets colour the columns based on number of Events from Last year

LY Events = CALCULATE([Number of Events],SAMEPERIODLASTYEAR('Date'[Date]))

This is great because we now get an idea of how different last year was, But without any legend information its quite difficult to understand the new information

Lets add a legend

Here is where the 2020 updates come into play. we can now see that the colour is related to Last year events and it goes from 0.9K up to 1.2 K so we can see that in 2019 July had the highest number of Events.

(I have set the colour to red because in the context of our report, More events isnt actually a good thing)

Just remember, you cant add a legend data item into your visual if you are going to do this, but this is a really great new feature

Power BI why marking a date table is a good thing

We are looking at a Star Schema with a Date table connected to the Fact table by Order date Key

for these examples, the active relationship is the only one that matters

Lets have a look at all the issues arising from using the Date table without marking at a Date table

Date Slicer seems to remove the report filter flag for the last 3 years

Rolling 3 year flag= IF(DATEDIFF('Date'[Date].[Date],TODAY(),YEAR)<=3 && DATEDIFF('Date'[Date].[Date],TODAY(),YEAR)>=0,1,0) 

This flag has been added to the entire report and set to 1 to make sure there is only information from these 3 years

 If I add date as a table I get 2017 2018 2019 and 2020 as expected for a rolling flag. 

However As soon as I change that table into a Slicer, all the years appear in the data set.

In this case from 2016. The filter stops being applied on the slicer

DAX FUNCTION SAMEPERIODLASTYEAR and the date hierarchy

We have some DAX to create SAMEPERIODLASTYEAR.

This is the DAX

LY Total = CALCULATE([Total],SAMEPERIODLASTYEAR('Date'[Date].[Date]))

Note the .[Date] at the end. Because we are using the date hierarchy to create the measure you can choose which level of the hierarchy to use. In this case we are saying we want the date level.

This will come back later

This is used in a visual and we have added Month Year which is a column in the date table.

As you can see, last year displays exactly the same as the current measure. This does not work

ALLMONTHSSAME.JPG

it works using the DateTime Hierarchy in the visual? However in this instance this date time hierarchy isn’t required. We don’t want the user to have to drill down to month from year every time.

In this example, the only field you can use for any measure that is date time specific is the date field

Mark as Date time

Always make sure you have a date table connected to a fact table

Note that the Date is now not connected to a back end hierarchy table.

Marking as Date table means that all your data within this table can be used for Date Measures

However lets look at the DAX that was created

All the Lat year measures are now erroring. this is because of the .[Date] at the end of the DAX

The date does not contain a hierarchy any more so if you have used .[Date] This needs removing. Its specifically related to the hierarchy.

A Year to day hierarchy has been created in the date table. This means that you have created a more compact data model and saved space.

And you can now use Month Year on the Axis

Date Table is Marked. How to work with it

This is part of the star schema in Power BI

Now the date table is marked as date, the Date hierarchy table is removed for Date within the Date table. this saves space and you can simply create your own date hierarchy to work with

DateDim1.JPG

The Active Join is on Received Date

All my measures so far are based on received date

Each Date in the fact table creates an extra date table to produce the hierarchy. So to save space, you should create inactive joins to the other dates and then remove the dates in the Fact table leaving just the keys. the model should then reduce in size

DateDim2.JPG

This works great for the measures. I can create measures based on the none active ones and simply choose to USERELATIONSHIP

LY Totals by Closed Date = CALCULATE([Total],USERELATIONSHIP('Complaint Metrics'[Closed Date Key],'Date'[Date Key]),SAMEPERIODLASTYEAR('Date'[Date]))

The above is an example of using the Closed Date None active join for Last years Totals

So i can have measures for Received this Year, Received Last year and Closed this year , Closed Last year (For Example)

This is all absolutely spot on. However there is more logic we need to think about. What about when the users want to create a drill through

visualForDrillthrough.JPG

This visual was created on the active relatioship so its recieved date

However your users may want to drill through to the following

DrillThrough.JPG

how to do this?

Currently you may have the Active join on received date and Inactive on Start and Closed date

In my example, I have also kept the dates in the fact table along with the Date Keys for each Date.

Because they are in the fact table they all have date hierarchies.

Remove the Date Hierarchies across your entire dataset

You could keep them there and simply remove their hierarchies

File > Options and Settings > Options

You can turn off time intelligence for the specific report OR globally. the recommendation is to actually do this globally when you use a date table

This way you can use the dates in the fact table for drill down without creating unnecessary data with the date hierarchies

Role Play dimensions

If you want to drill down to all your dates on a regular basis, AND if there isn’t one main date and other dates that are not used as often.

In Power Query Editor go to your date table and create a reference table for each date

In this case the Date table has been referenced once and is named date created. date as been renamed to date Engagement

with this set up, there is only one join to each date key so no inactive relationships.

Any DAX you create can reference the correct date tables and your Dates can be removed from the fact table

the big downside is you have a much more complex schema with many more dimensions so only go for this if your dates are all frequently used.

In the example above, the user just wants to drill through and see all the dates so they can be left in the fact table in a Date Folder without their hierarchies

But this has been a really useful bit of research on marking the date table, Date Hierarchies and role playing

Create your website with WordPress.com
Get started