Important Concepts In Power BI

Direct Query

• You are querying data while running the report.

• Data is not imported into Power BI, but querying all the time to the data source

• Use Direct Query when want live data, and want to have a small size of the data model stored.

• Do not use Direct Query if want to have great performance with large data sets, or want to use a lot of Power Query in Power BI.

Import

• Data is imported and cached in Power BI as an Extract.

• Querying are done through the imported data source and not to the base data source.

• Use Import Data when you do not need live data and is okay with storing the data.

• Use Import data when want to have great performance with large data sets, be able to use Power Query, or combining multiple Data Sources.

In the Data View in Power BI Desktop you can:

• Investigate which columns and data that you have in your different tables.

• Look at the data after is has been transformed in the query editor.

• Filter data and look at specific values

• Sort and format data

• Create new tables, formulas or measures

In the Model View in Power BI Desktop you can:

• Create new relationships between different tables

• See a visual representation of all the relationships inside the model

• See which way the relationships are, and if one to many, one to one or many to many

• Add layouts to easier work with smaller parts of the model, if there is a big model

• Group different fields into folders inside the tables

• Hide fields, add names and add descriptions to different fields

• Format different fields

In the report view in Power BI Desktop you can:

• Create the visualization/visual part of the reports, which means…

• Create report pages with different visual aspects such as charts, tables, Big Ass Numbers

• Format different aspects of the visualizations such as the labels, titles, legends etc.

• Create DAX measures

• See if a “column” is a calculated column, explicit measure, implicit measure or other.

• Add filters to the report

See my blog post about this for more information

What are Calculated Columns?

• Calculated Columns can be added in the “Data View” by pressing “New Column”.

• The column will be added and stored as an extra column in the chosen table.

• Calculated columns calculate values Row by Row (Important concept!)

• Calculated columns are useful in slicers.

• Calculated columns are only computed once, when they are created and never again -> This means that they take up precious space in the RAM memory!

• One reason to not use Calculated Columns, but measures is…..you guessed it….take up less RAM memory.

• If one can use calculated columns and measures for somethings, always use measures.

What are Measures?

• It is easiest to create measures from the Report Page through the formula bar.

• A measure is not stored in a table.

• Need DAX Studio or include in a visualization to see how it looks.

• Measure is not stored in RAM memory and is calculated at query time.

• Measures must be used when calculating ratios of aggregates, i.e percentages.

• Measures are difficult to understand at first, as they do not “Operate in Row Context” automatically.

• Always use measures if you can use both measures and calculated columns for something.

Power Query

• Power Query is an engine inside Power BI, and Excel, that one can utilize to transform and prepare data.

• With the tool, Power Query Editor, one can use a graphical interface to perform these ETL-processes.

• One can also perform this programmatically with the help of the M-language.

• Some of the things you can do in the Power Query Editor are:

  • Connect to different data sources

  • Combine different data sources

  • Transform different data sources.

  • Save the transformation, similar to a macro, to perform the same transformation again if the data is updated.

  • Create functions to use on different data sources

  • Utilize parameters to change connection for example between test and production servers.

  • See the statistical distribution of the columns

  • See the column profiles and column quality

  • Create new columns, which can be done with formulas.such as index, bins and more.

  • Change the data type of columns

  • Pivot and Unpivot columns

  • And muuuch more

Some of the most common DAX functions that I use are:

• SUMX: Sum a column or an expression, very useful iterator, Example:

  • Total Sales := SUMX(‘Sales’, ’Sales’ [Quantity’] * ’Sales’ [Price]])

• CALCULATE: Apply filters to Expressions and measures, Example:

  • Swedish Sales := CALCULATE(Total Sales, Geography’[Country]=”Sweden”)

• DATESYTD: Total dates up to last available date, used in Example (With Calculate):

  • Sales YTD := CALCULATE ([Total Sales], DATESYTD (‘Date’[Date]))

• DATEADD: Can manipulate to get dates in other periods, Example (With Calculate):

  • Sales PY := CALCULATE([Total Sales],
    CALCULATETABLE(DATEADD(‘Date’[Date], -1, YEAR)
    ,’Date’[DateWithSales] = TRUE)))

You can publish a Power BI report to Power BI Server by:

• Open Power BI Desktop, and navigate to the report you want to publish.

• Sign in to an account associated with a Power BI Server in the top right corner.

• Go to the Report View in Power BI Desktop and click the button that say Publish

• You can publish in “My Workspace” or a workspace that you have created/have access to.

• You can press the link that comes up to go directly to Power BI Server and see the report.

An App is similar to a workspace, but is only used to distribute reports from a specific workspace.

You can create and publish an Power BI App by:

• Go to a workspace in Power BI Server where you want to send reports to an App.

• Press “Include in App” besides the reports that should be included in the App.

• Click “Create App” to the upper right of the said workspace.

• You will get a screen where you have to answer some setup questions about the app, such as

  • App Name

  • Description

  • (Optional) logo

  • Theme Color

• You will get another screen to answer navigation questions such as:

  • Order that reports will be shown in the App

  • Possibility to Rename Reports in the Apps.

• Finally you will get a third screen to answer permission questions about the app, such as:

  • Who should have access to the app.

  • What will the people that have access to the app have the possibility to do?(Connect to datasets, share app etc.)

• Share the app with your organisation by:

  • Press the Get Apps Button below Apps in Power BI Server and search for the app that you (Someone in the organization) want to see.

  • Click Get Now when they see the app (If they have access to see the app).

If I would give a short summary of the list below, Power BI Pro can be used if you can manage model sizes < 1 GB, max 8 refresh per day/report, 10 GB storage per user, do not need paginated reports, deployment pipelines, data flows or XMLA endpoints.

You can do a lot with Power BI Pro and I think that a lot of small organizations use this.

Power BI Premium Per user is something of a middle ground between Power BI Pro and Power BI Premium, where you can still pay the per user price, but get some of the premium capabilities such as paginated reports and application life-cycle management.

Power BI Pro

• Price: 14 Dollar per User, per Month

• Mobile App Access and possibility to publish to Power BI Server

• No Paginated Reports

• 1 GB model limit

• 8 Refreshes/Day

• A lot of data sources, AI, API etc.

• Metrics for content creation

• No DataFlows

• No XMLA Endpoints

• 10 GB Storage per user

Power BI Premium Per User

• Price: 28 Dollar per User, per month

• Everything in Power BI Pro +

  • Paginated Reports

  • Advanced AI

  • XMLA Endpoints

  • DataFlows

  • Application Life Cycle management

  • 100 GB model size

  • 48 refresh/day

  • 100 TB storage/user

Power BI Premium

• 6858 Dollar per capacity/month

• Everything Power BI Premium per User +

  • 400 GB Model Size

  • Consume contents without per-user license

  • On-premise report with Report Server

  • Autoscale Add-on availability

  • Multi-Geo deployment manager

Implicit Measures

• Implicit Measures have the Sigma/Sum Icon in front of them.

• They are set by Power BI as summarized automatically and Power BI will always summarize these columns instead of individual values.

• You can change the implicit measure to Summarize as other ways than total sum, such as:

  • Average, Minimum, Maximum, Count, Count(Distinct)

• But you can not do anything more than these basic calculations with Implicit Measures

• You cannot use any implicit measures when using Analyse in Excel in Pivot Tables

Explicit Measures

• Explicit measures are created with DAX code

• Example: SUM(‘Sales’[Amount])

• Explicit measures can are more complicated than implicit measures as you have to write them yourself.

• But they are much more flexible, as you can do other things than just the different ways of aggregation

• You can also reuse Explicit measure for example when calculating the margin

• You can use Explicit measures in Analyse in Excel.

• There is no difference in performance between Explicit and Implicit measures (If they are doing the same as sum of sales).

• Hide the column (Implicit measure) if you are creating an explicit measure with the same function

• I always move my, Explicit, Measures to a “Measure Group” (A Table with only Measures)

• I only use Explicit Measures

Quick Measures

• Quick Measures are Explicit Measures without having to write any DAX code yourself.

Automatic Filters

• Filters automatically added when building a visual.

Manual Filters

• Drag and drop to the filter pane while editing a report

Include/Exclude Filters

• Right click and press include/exclude on a visualization and they will automatically get added to the filter pane.

Drilldown filters

• If you have the drill-down functionality and press on the dimension you want to drill down, these filters are automatically added to the filter pane.

Cross-Drill filters

• Drill down filter but connected to another visualization, will be added to the filter pane.

Drillthrough filters

• Drill down filter but connected to another page, shown up in the “Drillthrough pane”.

URL filters

• Add an URL query parameter.

Pass-through filters

• Filter created through Q&A.

Deployment pipelines is a life cycle management tool in side Power BI.

• To utilize Deployment pipelines one need to have at least Power BI Premium/User

• Deployment pipelines are a tool inside Power BI Server

• Deployment pipelines divide the reports in the three different categories:

  • Developement (Build up the reports and upload them here)

  • Test (Share content with other people to test, increase amount of data in the models)

  • Production (Move here when the final report is ready)

Row-Level Security is great to use when you have confidential data, and want to to show different views for different people without creating copies of a report:

• Static RLS

  • You create one role for each new view you want.

  • If you have 100 different sales people, you will need 100 different roles.

  • Example: [Country] = “Sweden”

• Dynamic RLS

  • You create one role, based on the person that is logged in to Power BI.

  • If you have 100 different sales people, you can handle this with only one role.

  • Example: [Username] = USERNAME()

There are three main types of relationships in Power BI, many-to-many, one-to-many and one-to-one.

• Many-to-Many

  • When you have no unique identifier/key in either of the tables

  • Try to avoid this as much as possible, as this can lead to ambiguity in your model.

  • In some rare cases, like working with RLS, many-to-many relationships might be needed

• One-to-One

  • This means that both tables have an unique column that is used in the relationship.

  • This is usually not that common in production models.

  • The reason is that you can usually combine the two models when you have one-to-one relationships.

• One-to-Many/Many-to-One

  • This means that one of the table have an unique key, while the other table have duplicates of these key.

  • This is usually the relationships you aim for in your models, as they optimize the performance of the models most.

  • By creating fact and dimension tables, one-to-many relationships will be created automatically if these are created in the correct way.

  • The fact tables will be the many part of the relationships, while each of the dimension tables will be the one part of the relationships.

Bidirectional filters are filters that works in both directions.

It is used in case where you either have One-to-one relationships or many-to-many relationships.

You might already have a though about if it is good, or bad, to use bi-directional filters if you have knowledge about these two types of relationships.

I would advise to use Bidirectional Cross Filtering as little as possible, as this will affect the performance of your model.

But let us go through the benefits and disadvantages of bidirectional filters.

• Benefits of Bidirectional Cross Filtering

  • You can apply filters to both sides of a relationship.

  • Can be more detailed in the reports that you are sending out.

  • Do not need complex DAX formulas to change the direction of filters.

• Disadvantages of Bidirectional Cross Filtering

  • The performance of the model will be slower.

  • You might have ambiguity of the data and get results that you do not understand

  • Try to use DAX measures such as CROSSFILTER instead of having the bidirectional filters in your model.

What is DAX?

• DAX Stands for Data Analysis eXpression and is a functional programming language.

• It is used in tools such as Power BI, SQL Server Analysis Server and Power Pivot for Excel.

• The Syntax is very different from other programming languages, but mostly resemblances with SQL.

• According to Marco Russo and Alberto Ferrari “A matter of querying a set of tables joined by relationships and aggregated values”.

• The most dangerous thing with DAX is that you can write things that works, long before you understand what is happening behind the scenes.

• DAX Formulas can be written in Power BI, while DAX Queries only can be written in external tools, such as DAX Studio.

• The main building blocks of DAX are Calculated Columns, Calculated Tables and Measures.

Advanced concepts in DAX are

• Evaluation Context

• Filter Context

• Row Context

• Context Transition

• Reasons to learn DAX are:

• If you are not utilizing DAX well, you are missing approximately 90% of the Analytical capabilities of Power BI

• You can evolve your Power BI Reports dramatically with DAX

• DAX is fun!, and the more you learn the more fun will you have on your journey

• You get more dynamic and can understand performance issues better by learning DAX.

Aggregators

• Example: SUM(‘Sales’[Quantity])

• Functions that aggregate the values of a column in a table and returns a single value.

• They can work by only taking in one parameter.

• Examples of aggregators are:

  • SUM

  • MAX

  • MIN

  • AVERAGE

Iterators

• Example: SUMX(‘Sales’,’Sales’[Quantity])

• Requires two parameters to work, in this case both the table and the expression

• The first parameter is always the table to “scan” and the second parameter most often an expression.

• Actually, all aggregators are iterators, but Power BI have utilized “Syntax Sugar” to write them in a shorter version.

• Examples of iterators are:

  • SUMX

  • MAXX

  • MINX

  • AVERAGEX

Iterators and aggregators, such as SUMX and SUM, can be used interchangeable when the expression is a single column, but if you learn to use the Iterator here as well, it will come more natural to work with iterators in more advanced concepts where Iterators are a must.

Besides Iterators and Aggregators, there are also “A-functions”, such as AverageA, but it is safest to not use them as the result of these are inconsequent.

Power Query

• Power Query is an engine inside Power BI, and Excel, that one can utilize to transform and prepare data.

• With the tool, Power Query Editor, one can use a graphical interface to perform these ETL-processes.

• One can also perform this programmatically with the help of the M-language.

• Some of the things you can do in the Power Query Editor are:

  • Connect to different data sources

  • Combine different data sources

  • Transform different data sources.

  • Save the transformation, similar to a macro, to perform the same transformation again if the data is updated.

  • Create functions to use on different data sources

  • Utilize parameters to change connection for example between test and production servers.

  • See the statistical distribution of the columns

See the column profiles and column quality

Create new columns, which can be done with formulas.such as index, bins and more.

Change the data type of columns

Pivot and Unpivot columns

And muuuch more