DAX Cheat Sheet

What is DAX?

It is the programming language for Power Pivot, SSAS Tabular and Power BI.

It resembles Excel because it was born in PowerPivot. But it has no concept of <row> and <column> and has different type system.

The most important, it has many new functions.

The most two are measure and calculated column:

Measure is used to calculate aggregates, e.g., Sum, Avg and evaluated in the context of the cell in a report or a DAX query.

Calculated column evaluates each row and is computed at the low level within the table it belongs to.

Some Common Dax Expressions:

LOOKUP:

  1. Return the value in result_columnName for the row that meets all criteria specified by search_columnName and search_value
  2. LOOKUPVALUE(Result Column Name, Search Column Name, Search Column value)

FILTER:

  1. Return a subset of a table or expression
  2. FILTER(<table>,<filter>)

ALL:

  1. Return all the rows in a table, or values in a column, ignoring any filters that may have been applied
  2. ALL(<table> or <column>)

RELATED

  1. Returns a related value from another table
  2. RELATED(<column>)

CALCULATE

  1. Evaluates an expression in a context that is modifies by specific filters
  2. CALCULATE(<expression>,<filter1>,<filter2>)

If you are interested in or have any problems with DAX or Business Intelligence, feel free to contact me.

Or you can connect with me through my LinkedIn.

How to do web scrapping in Power Query in Power BI?

On web, there are public data sourse which can be imported and transformed to make insight reports or dashboards.

There are two circumstances:

This kind of importing of tables is super easy.

For example, we can try to make an easy one.

Here is the web link: https://en.wikipedia.org/wiki/Demographics_of_China. What we want is to import the tables from a Wikipedia webpage.

The simple steps are as follow:

Open Power BI Desktop–>Click Get Data–>Click Web–> Paste the URL in the dialog and click OK.

So, there is the screenshot:

In Navigatot dialog, we can select the corresponding table that we want to import and transform it.

This kind of importing from web in Power BI is simple. Because Power Query can identify the table in HTML and tables are in the <table>…</table> tags.

However, many tables in HTML are different.

If we meet this kind of circumstance, we will end up seeing the Document entity in the Navigator.

Next blog, we will demonstrate how to extract the relevant elements in HTML with Power Query in Power BI.

The tricky part id how to find the correct path in the HTML source tree, which needs you to have a basic but not prior HTML knowledge.

To motivate you to keep reading, here is the final web scarping screenshot which i made recently:

If you are interested in or have any problems with Power BI or Power Query, feel free to contact me.

Or you can connect with me through my LinkedIn.

An Introduction to Power BI (Power BI 101)

This article we will provide an introduction to Power BI.

It is a Microsoft business analytics service to provide interface to create reports and dashboards with interactive visualisations.

It has an advantage: easy to use (self service BI).

First of all, Power BI official guided learning material.

It’s very structured and good training tutorial.

As I talk in previous blog about this learning platform, it is fun to get points and badge.

What is the workflow of Power BI?

  1. Bring data in using Power BI desktop, manipulate data and build reports.
  2. Publish it to Power BI service.
  3. Share reports and dashboards to others.

Here we go to the first step:

Download the Power BI desktop.

Power BI desktop version provides data warehouse capabilities:

  1. ETL
  2. Calculates column
  3. Measures

After we download it, we can find there are five panels in Power BI interface:

  1. Fields: where the datasets
  2. Data: view and manipulate the data
  3. Reports: place visualizations to build reports
  4. Dashboards: choose the graphs to use
  5. Relationships: view/change relationships in the dataset

Through these panels, we can manipulate data and build reports.

Once the report is completed, we can publish it onto Power BI Service on the cloud.

Reference sources:

https://www.sqlbi.com/ref/power-bi-visuals-reference/

If you are interested in or have any problems with Power BI, feel free to contact me.

Or you can connect with me through my LinkedIn.