Tag Archives: DAX

ByHariharan Rajendran

Capture the Selected Filter in PowerBI – DAX

As PowerBI has various options to slice and dice the data, it is very mandatory to understand the filter context on the PowerBI report.

Filter Context can be applied on various levels

  1. Row & Column Headers
  2. Slicers
  3. Filters (Visual, Page & Report level)

Let us take a scenario where the user wants to capture the filters applied to the specific field.

To demonstrate this scenario, I create a simple table as like below.

I need to use Description field on the table to filter the data.

Description field on Slicer

If the user selects the checkbox then it should show the selected value. If it is a single selection, then show the value. If users select multiple values, then show as “Mulitple”.

Description field on Filter (Page level Filter)

Place the description field on the page level filter and see the outcome.

Use the below DAX script to achieve the result.

Parameter Selection =
IF (
HASONEVALUE ( Parameters[Value] ),
“Selection” & VALUES ( Parameters[Description] ),
IF (
NOT ( ISFILTERED ( Parameters[Description] ) ),
“No Selection”,
“Mulitple Selection”
)
)

ByHariharan Rajendran

Year to Date calculation in DAX

This post explains how to achieve Year to Date calculation using DAX script.

In DAX, there is a pre-defend function available to get the Year to Date calculation. Also, for the quarter to date & month to date.

Those functions are

TOTALYTD()

TOTALQTD()

TOTALMTD()

Instead of using the pre-defend functions, we can use our own defined function like below. To achieve the YTD calculations, use the below functions

  1. CALCULATE
  2. FILTER
  3. ALL

Let us consider a field called “Total Sales”. We will be used the Total Sales & “Date” table.

YTDSales = CALCULATE([Total Sales],

                      FILTER(ALL(‘Date’),

                          ‘Date'[Calendar Year] = MAX(‘Date'[Calendar Year]) &&

                          ‘Date'[Date] <= MAX(‘Date'[Date])))

Compare the result TOTALYTD() with our script.

Explaining the script.

Let us consider that we are in row 2014 – March row on the above table.

YTDSales = CALCULATE([Total Sales],

FILTER(ALL(‘Date’),

‘Date'[Calendar Year] = MAX(‘Date'[Calendar Year]) &&

‘Date'[Date] <= MAX(‘Date'[Date])))

 

‘Date'[Date] <= MAX(‘Date'[Date]) => This line of code returns maximum date. In our case, It will return 31/3/2014

‘Date'[Calendar Year] = MAX(‘Date'[Calendar Year]) => This line of code returns maximum year.  In our case, it will return 2014

ALL(‘Date’) => This line of code returns all the dates from date table.

FILTER(ALL(‘Date’),

‘Date'[Calendar Year] = MAX(‘Date'[Calendar Year]) &&

‘Date'[Date] <= MAX(‘Date'[Date])))

The above line of codes returns, current row year and last date of the current row month.

 

ByHariharan Rajendran

DAX – RELATED & RELATEDTABLE

This post explains the concept of below DAX functions

  1. RELATED
  2. RELATEDTABLE

I need to explain the data modelling little bit before going to DAX functions. As you know, we need to create a relationship either manually or automatically to use the tables together to get the expected result.

We have 3 different relationships available.

  1. One-to-One Relationship
  2. One-to-Many Relationship
  3. Many-to-Many Relationship

In the context, let us consider a one-to-many relationship. If you have CUSTOMER master and FACT tables then trying to create a relationship, then that would create a one-to-many relationship.

It means we have unique records on the customer table but multiple same values on the FACT table with reference to the connected columns.

If you want to create a new calculated column on the CUSTOMER table then you need to use RELATEDTABLE DAX function. On the other hand, when you want to create a calculated column on the FACT table then you need to use the RELATED function.

RELATEDTABLE:

Customer First Purchase = MINX(
RELATEDTABLE(‘Sale’),
‘Sale'[Invoice Date Key])

 

RELATED:

State = RELATED(‘City'[State Province])

ByHariharan Rajendran

DAX Leap Year In Power BI

Identifying the Leap is very simple logic, this post explains that how you can identify a year which is a leap year or non-leap year in Power BI.

Consider I have a year column on my table. If not then use the Year function on the data column and get the year column.

You need to find the below values,

  1. Year Start Date
  2. Year End Date
  3. Days between Year Start Date and Year End Date.
  4. Check the number of days

That’s it, very simple.

Use the below DAX expressions to identify the above values.

YearStartDate = DATE(‘Year'[Year],01,01)

YearEndDate = DATE(‘Year'[Year],12,31)

TotalDays = (‘Year'[YearEndDate]-‘Year'[YearStartDate])*1+1

IsLeapyear = IF(‘Year'[TotalDays]=366,”Yes”,”No”)

Happy Learning.

ByHariharan Rajendran

DAX Query CALENDARAUTO()

In Power BI, you can use the pre-defined function CALENDARAUTO() to create date table with a column called “Date”. It will generate a date for current 1 year by default.

This above function will only work when you have a model with at least one column with date or datetime data type.

In the below screenshot, I tried to use CALENDARAUTO() function in an empty model.

You can see the warning message above.

“CALENDARAUTO() function can not find a base column of DateTime type in the model”

In the same scenario, I just added a simple table with one date data type column then I used the CALENDARAUTO() in a new table and this time I got the expected result.

Happy Learning. Share your comments.

1