What are slicers ?

What are filters?

How are they different from each other?

What and When should be used?

Well lets see,

Slicers and filters are almost the same except for some small differences. And which should be used at which time totally depends.

• Slicers are displayed on the report canvas, so it provides a clear picture on what the data is all about and whether the data has been filtered or not and if so, what are the filters at a glance. Whereas filter selections need be expanded to be used as well as to see the filters used.
• Slicers are available in the report canvas consuming report space, where are filters are not. So if you have lot of visuals and have concerns on slicers consuming more space then go for filters.
• If the filters need to be used frequently for the reports then slicers are the best choice, unlike filters considering the ease of access.
• Filters can be set for:

Visual level

Page level

Report level

Suppose you need to set different level of filters for the visuals in a page, simply avoid filters for some visuals this can only be obtained by slicers.

For an example in the given below example I want the units sold by country visual to represent units sold across all the countries. So, this can be easily obtained by setting filter none in edit interactions.

Just click on the relevant slicer in the example the country and click on “Edit Interactions” (Visual Tools -> Format -> Edit Interactions) and click on the None icon for the chart which you do not want to be filtered.

Using filters visual level filters can be provided, where you need to set the filters for each and every visual individually.

• Filters provide a range of options as Top N, Bottom N, Less than, Greater than, Less than or equal, etc.

But Slicers only provide a limited number of filtering options.

• Sync Slicer option

Once you create the slicers on one page, of course you can copy and paste the slicers on to the other page. But what if you have a handful of pages in your report? And what if you have a considerable number of slicers as well. Well that’s where the Sync slicer option comes in handy, with some other features which adds some icing to the piece of cake.

With the Sync Slicer option, the slicers set in one report can be synced to the other pages of the report. So not only you can duplicate the slicers on to the selected reports with just couple of clicks, but also enables you to sync them for better user experience.

This can be done in 3 ways.

Method 1

First enable the option Sync Slicer under view.

Then click on the relevant slicer. Now in the Sync Slicer pane we can enable the sync of the slicers in the other reports as well.

In the example given above The country slicer is available in page 1,

is also available and visible in page 2 and page 4. (Note the checkbox under the eye symbol).

In page 3 the country slicer is not visible but the data is synced. So if the user has selected France in page 1 page 3 will also reflect the data related to France.

In Page 4 the Country slicer is available, but it is not synced with the Page 1 country slicer data. So the country selected in page 1 will not be passed to the country slicer in page 4.

So, on and so fort the slicer option provides a bit flexible operations when compared with filters.

Method 2

First enable the option Sync Slicer under view.

And under advance options give a name to the sync group and you can give the same name to any slicer, which uses the same column and those slicers will be synced.

Method 3

Of course, you can copy and paste as the traditional way. And then power bi will prompt a question asking if you want to sync the two slicers just as given below. Which you can change anytime you want.

So likewise slicers and filters have unique features as well as different types and different levels of limitations. What and where to be used is the totally the developers call.

So think wise and work wise.

 

 

Lets look at the storage modes of Power BI Data sources.

There are 3 types of storage modes as:

1. Import
2. DirectQuery (DQ)
3. Dual

Based on our example all the tables including FactInternetsales table is in DirectQuery Mode, since we used DirectQuery to retrieve data in the first place.

How about Sales Aggre?

Well when creating the aggregated table what Power BI does is it imports the relevant columns (Column used for grouping as well as the newly created aggregated columns) and creates a smaller version of the original table FactInternetSales, and saves it in the memory just as it is done when data is being imported into Power BI.

The 3^rd type of storage mode is Dual storage. It is a hybrid version of the Import and DQ, which enables tables to act as either Import or DQ based on the relationship to the other tables.

Since DimDate, DimCustomers, and DimProduct tables are related to both the Sales Aggre and the FactInternetSales tables, lets set the storage mode of these 3 tables into Dual mode.

Apart from these tables any other tables, which have relationships with both the original and these aggregated tables, will also be converted into dual mode by Power BI and a notification may pop-up stating this.

So, as a result of creating an aggregated table possible we’ll end up having a composite model with tables in all types of storage modes:

FactInternetSales – DirectQuery Mode

Sales Aggre – Import Mode

DimDate, DimCustomers, and DimProduct – Dual Mode

Any other tables, which have relationships with both FactInternetSales and Sales Aggre – Dual Mode

All other tables – DirectQuery Mode

 

 

An Aggregation table is simply a smaller imported version of the original large DirectQuery table, which also can be considered as a solution for performance optimization when using larger DirectQuery tables.

Creating an Aggregated Table

Now we will be creating an Aggregated table for FactInternetSales.

In order to do so,

Go to Edit Queries and create a reference table from the FactInternetSales table.

We have renamed the Reference table as Sales Aggre.

Now lets select the Sales Aggre table and select Group by under Transform.

The advance option enables to group by based on multiple columns.

As shown in the example click ok once the name for the measures, the aggregation desired and the column to be the aggregated is provided.

The column is unnecessary when a count of rows is to be aggregated.

Note: It is mandatory for the data types of the original source table and the aggregated table to be matched.

Ex:  FactInternetSales[SalesAmout] -> Decimal i.e.: Sales Aggre[TotalSalesAmout] -> Decimal

It is also mandatory to set the data type to Whole Numbers for the aggregated Count of Rows columns.

Create the relationships among the aggregated table and the Dimension tables based on the key as Usual as shown above.

Once the Aggregated table has been created the next important step is to configure the aggregations.

The manage Aggregations option can be found when right click on the Aggregated table.

At this step the columns which are used for grouping, the aggregated columns as well as the aggregations should be configured as given above.

As the final step hide the aggregated table. The table will be set to hidden automatically. If not set it to be hidden from the report view.

Now all is set to go.

Whenever the aggregations created in the Sales Aggre table are used in the report the Power BI will switch to the smaller aggregated table, which is in imported mode and when further data is required, which are available in the Original FactInternetSales table Power BI will switch to the original table in DirectQuery mode. And the best part is all these performance related activities are executed in the backend automatically.

 

 

 

What is a composite model in Power BI?

Composite model is a data model, which is built on a combination of imported data and DirectQuery data.

In short, Power BI composite model means a part of the data model having data from a DirectQuery to the data source and the other part being an imported data.

Given below is an example for a composite model.

Among the tables:

DimCustomers is in import mode and all the other tables including FactInternetSales Table are in DirectQuery mode.

Which tables in which mode is totally the developers call, which can be decided based on the data structure and the frequency of data refresh.

 

Building and using a composite model enables to obtain a mix of the advantages of DirectQuery and Import data sources.

When designing and implementing the data model,

The import mode can be considered where data rarely changes to enhance the performance of the visuals in the report as well as the DirectQuery Mode can be considered for Dimensions, Facts or tables where constant data refreshing is required.

What is data refresh scheduling?

The datasets used to build dashboards/reports should be updated in a timely manner as the data in the source will continuously get updated. This update can be scheduled, so that the data will refresh providing up-to-date data to the end user.

Why data refresh scheduling is so important?

The availability of latest data in the report is a critical requirement when it comes to reports and dashboard since it has an impact on the accuracy of the decisions taken using the reports or the dashboards.

The data on the other hand will keep on changing and updating.

When the data should be refreshed?

The changes and the frequency of the updates totally depend on the type of the data and the type of the domain. So the data refresh can be scheduled based on the frequency of the data updates.

How to schedule data refresh in Power BI?

Power BI playing a leading role among the modern reporting tools, also provides the data refresh scheduling feature in a simplest manner, which enables the Power BI self-service more meaningful and easy for the business end users.

The following are the easy steps to be followed to schedule a data refresh.

• Before the data refresh scheduling the credentials for the data-set should be provided in the web portal.
• Now under the relevant work space, in which the data-set is available click on the data-set and the click on the scheduled refresh.

• Enable the refresh schedule and select the refresh frequency (Daily, Weekly)

• A required time slot also can be set.

Note: A data-set will be considered as inactive when no users has visited the dashboards or reports built using the data-set over a period of 2 months. The refresh schedules of Inactive data-sets will be set to off and a notification mail will be sent to the data-set owner. By visiting the dashboards/reports built on such inactive data-sets the refresh schedule can be activated.