Tableau (Fixed - Include - Exclude) & DAX in Power BI - Concepts

LOD - (Definition)

"Level of detail expressions (Also known as LOD expressions) allows you

to compute values at the data source level and visualization level.

However, LOD Expressions gives you even more control on the level of granularity you want to compute.

They can be performed at a more granular level (INCLUDE), a less granular level (Exclude),

or an entirely independent level (FIXED)."

1. Fixed : Independent of view.

2. Exclude : Minus from view.

3. Include : Add to View.

-----------------------------------------------------------------------------------------------------------------------

* Fixed LOD Function sits above the dimension filter and after the context filter.

Features :

* Only looks at the expression for granularity (ie dimension list)

* Independent of dimensions in view.

* Result is scalar. (A scalar variable, or scalar field, is a variable that holds one value at a time.)

* Not affected by dimension filter.

Syntax : {Type {Dimension List] : Aggregate}

Example 1:

Step 1: The following Fixed formula is fixed at the data source level or before the context filter.

Step 2 : If we add any dimensions to the view, the numbers won't change. As shown in the below view. As it is placed before context filter.

Context Filter - (context filter as being an independent filter. Any other filters that you set are defined as dependent filters because they process only the data that passes through the context filter.)

* Now if we pass category to the context filter and filter Furniture from the category attribute it shows only the furniture category related details.

The following syntax performs similar to Fixed LOD:

{Aggregated} - {SUM(Sales)} - Fixed Table - Scoped LOD

This is kind of grand total.

2. Exclude LOD :

Exlcude LOD Function comes after the dimension filter. Hence it will be affected by the dimension filters.

(Exclude LOD : Respects and recognizes all of the dimensions that are in the view. But removes the dimensions that are found in the expressions in the calculations).

Exclude LOD.

1. Dimension in view, minus what is in expression.

2. Result in ATTR

3. Affected by dimension filter.

One way of using Exclude LOD is to have your total column as columns. (EXCLUDE level of detail expressions are useful for ‘percent of total’ or ‘difference from overall average’ scenarios. They are comparable to such features as Totals and Reference Lines.)

Here we're excluding category attribute and aggregating the values, In-conclusion it will provide us the total value by excluding category. If you de-select any category from the filter it will aggregate for the remaining values.

Note : If you add any attribute to the view and expecting that to exclude values from the view, Then we should be including that attribute in the exclude formula.

3. Include LOD: Include LOD Function sits after the dimension filter.

Include LOD.

1. Dimension in view, plus what is in expression.

2. Result in ATTR

3. Affected by dimension filter.

INCLUDE level of detail expressions compute values using the specified dimensions in addition to whatever dimensions are in the view.

* The exclude lod subtracts or takes away a dimension in its own expression therefore it gives us a rolled up value. Where as the include lod includes more dimensions so it puts more detail in. Hence it is more granular and less aggregated.

Scenario : If in addition to sales you needed to display the average regional profit for each of these categories but the other requirement is not to show region.

In the upcoming article I will explain more concepts related towards the different used cases of LOD expressions.ending order.

-----------------------------------------------------------------------------------------------------------------------------------------

DAX Function

DAX, or Data Analysis expressions, is a formula language used in Microsoft Power BI to create calculated columns, measures, and custom tables. Once mastered, DAX gives you powerful control over visuals and reports, allowing for better performance and more flexibility.

DAX Functions :

Note - Functions are predefined formulas that perform calculations on specific values, called arguments, in a particular order. Each function has a specific syntax indicating the order of arguments to be expected. Dax has several different functions that fall into several categories, including Aggregation, Date and Time, Logical, Text, and many more.

* Function Categories.

> Aggregation - SUM(), AVERAGE(), COUNT()

> Date and Time - TODAY(), MONTH(), YEAR()

> Logical - IF(), AND(), OR()

> Text - CONCATENATE(), UPPER, LEFT()

and many more......

Points To Be Remebered

1. DAX is a formula expression language used in multiple Microsoft analytics tools.

2. DAX formulas include functions, operators and values to perform advanced calculations.

3. DAX formulas are used in :

* Measures.

* Calculated columns.

* Calculated tables.

* Row Level security.

The power of DAX.

It opens up new capabilities

* Join, filters, measures and calculated fields become part of your toolbox. (We can use joins, filters, measures and calculated fields to perform complex and create visualizations.)

* DAX + Power Query = a powerful data analysis tool:

1. Dive deeper into the data and extract key insights

2. Use DAX for rapid prototyping.

Calculated columns vs measures

Calculated Columns - Calculated columns can be very useful as they enable us to expand our existing dataset without editing the source data. Calculated columns evaluates at a row level and therefore adds a new column to an existing table. Any column that is created is calculated at data load or when the data is refreshed.

Calculated Measures - Calculated measures works a bit different. Measures enable you to create complex calculations that can be used on your data. Where columns evaluate for each row, measures aggregate multiple rows and adds a new field that can be added to a vizualisation. The measure itself is calculated at query time rather than when data is loaded, therefore gets updated as you interact and filter the data. This means it is more efficient because the calculation isn't run every time a table is accessed, instead only when it's being used. Measures can be created in two ways, by writing a DAX formula or by using Power BI's Quick Measure. We will get into Quick Measures later.

Quick Glance of Measure

* Enables Complex calculations.

* Aggregates multiple rows and adds a new field that can be added to visualizations.

* Calculated at query time as we interact and filter. (More efficient because the calculation is not run every time the table is accessed).

* Two ways to create a measure.

Write a measure from scratch.

User the built-in quick measure tool.

Calculated vs measure

ex:

To summarise - calculated columns evaluate for each row. It adds a new column to an existing table and is calculated at data load or when the data is refreshed. Whereas measures aggregate multiple rows. Results in another field that can be added to your visualization and is calculated at query time as we interact and filter.

Practical Examples

Now lets learn how to create a calculated column and measure in Power BI. I will using a single table that contains transactional information about commodities traded every day from Jan 2020 to March 2022. You can see all the available columns by clicking on the dropdown next to the table on the fields pane. We will start with creating our first calculated column - this can be created from inside the report or data view, by selecting our table and clicking New Column.

Our table commodities contains an Open price and a Close price but we currently do not see the trading difference for each day on each commodity. We create a new column called Trading Change followed by an equals sign. As we start to type Close, Power BI’s Intelligence leaps into action and shows us a list of all columns and functions that match our text. From here we can select Close followed by a subtraction sign then Open. Now we can commit this new column by clicking the checkmark or pressing enter

Lets Create Measure calculation field.

Note (we will start to create a multitude of measures, which can easily get lost in our data model. It’s best practice to create a calculations table in our model that acts as a folder for all our measures. It doesn’t contain any actual data, but rather a placeholder for all measures. The naming convention will always start with an underscore so that it appears top of the fields pane). When you create a new measure make sure that you have selected this table. We will create a new measure called MaxClosePrice which computes the highest closing price. For this measure, we will now use the MAX function which takes a column name

MaxClosePrice = MAX(Commodities[Close])

We can now see our new measure in our fields pane under the Calculations table. Now that we have our measure created - let’s visualize it. Let’s select a line chart from the visualizations pane and pull in our MaxClosePrice and Date.

*** DAX Calculations change depending on how the context is set ****

There are three types of context: Row, query and filter context.

Context enables dynamic analysis, where the results of a formula change to reflect the cell selection.

ex: If you exclude a sales region from a dashboard, the total sales will change.

* Row context - "The current row" - A calculated column is an example of where row context applies. In a calculated column the context includes the values from all columns within the current row.

* DAX calculated columns

COST = Orders[sales] - Orders[Profit]

-----------------------------------------------------------------------------------------------------------------------------

Filter Context :

1. The set of values allowed in each column, or in the values retrieved from a related table.

2. By using arguments to a formula or by using report filters on row and column headings.

3. Applies after query context.

ex: Total costs east = calculate([total costs], orders[region] = 'East')

-----------------------------------------------------------------------------------------------------------------------------

It is very Important to understand how different contexts will impact your visualizations.

--> Context is an important concept to understand when building and creating DAX formulas. Understanding how DAX evaluates functions will help with building more complicated analyses!

Calculate function

Calculate allows you to evaluate an expression with one or more filter contexts. The calculate functions takes two arguments - one argument is required and the other argument is optional. The first argument contains the expression to be evaluated - this must return a single value. The second argument for adding filters is optional - but you need to ensure that filters evaluate as a table, and do not clash with one another. For example, Sales City is equal to London and Sales Country is not equal to United Kingdom. The filters inside the calculate function will always override any filters from a visualization. You can see an example of calculate in action. Here we are calculating total sales in EMEA region.

Calculate Function:

* Syntax : CALCULATE(<expression>,[fliter1],[fliter1]).

Expression - A measure or calculation to be evaluated. Must return s single value.

Filters

* Filters need to be evaluated as a table.

* Filters should not clash with one another.

ex: Sales[City]="London" , Sales[Country]<>"United Kingdom"

CALCULATE() filters will always override from the visualization

* Example : CALCULATE(SUM(Sales), Sales[Region]="EMEA")

We’ll continue with our commodity dataset. We’ll be diving deeper into DAX measures, applying iterators, and learning how to use variables in a calculate function. we created a calculated column to work out the difference between the opening and closing price. Now we’ll create a measure that uses an iterator function to do the same calculation. First, we’ll start by creating a new Measure called Average Trade Difference and start by typing AVERAGE which will use Power BI’s intelligence to show all matching functions. We'll select AVERAGEX from the dropdown list. The AVERAGEX function takes two arguments, the table where the aggregation will be applied, in this case commodities. The second argument will be the calculation we are looking to perform on each row - in this case it’ll be Close subtracted by Open. We can now commit the measure by pressing enter. Let’s visualize this on a card - we can now see the average daily trade difference of 3.58 across all commodities.

1 AverageTradeDifference = AVERAGE(COMMODITIES, COMMODITIES[CLOSE] - COMMODITIES[OPEN])

We are going to learn how to use the calculate function. These are one of the most used functions in DAX, due to it’s ability to modify and have priority over the filter context. We’ll create a new measure called GoldVolume21. For this measure, we want to get the sum of all gold trades that occurred in 2021. In our calculate function, our first argument will be the sum of volume. Now that we’ve added our expression to be calculated, we can now add the filters we want to apply by using the FILTER function. The filter function takes the table to be filtered and the specific filter to be applied. Our first filter will be on the table commodities where symbol is equal to Gold. Next we’ll filter the same table again but this time filtering for the year 2021.

1 GoldVolume21 = CALCULATE(

2 SUM(Commodities[Volume]),

3 FILTER(Commodities, Commodities[Symbol] = "Gold"),

4 FILTER(Commodities, YEAR(Commodities[Date]) = 2021

))

The final concept we’ll be covering is a new topic we haven’t covered yet called variables. Variables are very useful when writing DAX calculations. As you write more DAX formulas, you’ll see that they can get complex very quickly. Variables help simplify your code as well as increase readability and performance. Variables can contain static values or snippets of code that may be re-usable in your query. To get familiar with variables, we can look at using variables in conjunction with calculate. We’ve created the measure GoldVolume21, but how about if we’d like to see the % growth in volume versus 2020. A measure has been created called NoVariable. This shows us a DAX formula that does not utilise Variables. If you look closely you can see that the calculate function is being used twice. This is inefficient for Power BI as it has to evaluate the same expression twice. Let’s copy the formula as we’ll be using this in our new measure. Let’s create a new measure that uses variables called Gold21vs20. First, let’s create a variable called GoldVolume20. Here we can paste the formula we just copied. Now we have a variable that calculates Gold Volume for 2020. We’ll now declare our RETURN statement that will compute our calculation. We will use the DIVIDE function to workout the % difference. For the numerator, we’ll calculate the difference between 2021 vs 2020. So GoldVolume21 minus GoldVolume20. Next, we’ll set the denominator to be GoldVolume20. We can now commit this measure and format it as a % and round it to 0. Now we can put this into a card visual and see that there was a massive increase of 480% in trades of Gold. We’ve seen variables, iterators and calculate in action. Now it’s your turn, let’s practice.

1 Gold21vs20 =

2 VAR GoldVolume20 = CALCULATE(

3 SUM(Commodities[Volume] ),

4 FILTER(Commodities, Commodities]="Gold"),

5 Filter (Commodities, YEAR(Commodities[Date]=2020))

6 RETURN DIVIDE([GoldVolume21]-GoldVolume20, GoldVolume20)

To be continued...........