Tag Archives: DAX

ByHariharan Rajendran

DAX – Adjust the Total value – Conditional Total in DAX

In this article, I want to show you how I achieved a business solution using DAX. Let me explain the requirement.

I have a below table and customer wants the data in the below format.

Country Color Quarter Product
India Green 2019-Q1 P1
USA Blue 2019-Q1 P2
UK Red 2019-Q1 P3
India Red 2019-Q2 P1
USA Blue 2019-Q2 P2
UK Amber 2019-Q2 P4

 

Expected Solution

Country 2019-Q1(Green & Blue) 2019-Q2 (Green & Blue)
India 100% 0%
USA 100% 100%
UK 0% 0%
Total 67% 33%

 

Actually, the customer wants to show the count as 1 or 0 if green or blue exists on each country so the required result would be like below.

Country 2019-Q1(Green & Blue) 2019-Q2 (Green & Blue)
India 1 0
USA 1 1
UK 0 0

 

First step: we can target blue and green count.

Green_Blue = CALCULATE(

COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

The next upgraded version is handling blank with zero.

GB =

Var GBVal = CALCULATE(COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal = BLANK(),

0,

GBVal)

Next level, we can divide the value to get the percentage.

GB =

Var GBVal  = CALCULATE(COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal  = BLANK(),

0,

GBVal  / CALCULATE(

COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”},

all(‘Product'[Quarter])))

Then finally, we need to tweak the result on the divide section to get the expected result. This will adjust the total section.

GB =

Var GBVal = CALCULATE(COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal = BLANK(),

0,

GBVal / if(

DISTINCTCOUNT(‘Product'[Country])>1,

CALCULATE(COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”},

all(‘Product'[Quarter])),GBVal))

We got the expected result. Please provide your feedback and comments.

 

ByHariharan Rajendran

DAX – Top N for each Previous & Current Year

This article covers a real-time scenario which was asked by one of the customers.

Requirement:

Show the sales of the top 5 countries for the current year and previous year.

Challenges:

The above requirement seems to be simple, but we don’t have the same customers on the previous and current year.

  1. If we use a simple bar/column chart, then we will get multiple standalone bars or columns.
  2. We can use a stacked column chart but if we apply the top 5 then it will apply on the whole dataset, but we need to apply for each year.

Solution:

Follow the below steps

  1. Create a DAX table with below logic
    1. TOP 5
    2. Filter – Current Year & Previous Year
    3. Union the above results

Detailed Steps:

I am using below dataset (Sales).

Country Year Value
A 2018 12
B 2018 34
C 2018 56
D 2018 17
E 2019 17
F 2019 19
G 2019 11
H 2019 23
I 2017 16
J 2017 18
K 2017 23
L 2017 26
M 2017 56

 

Used the DAX syntax to create another table

_Top5 = UNION(

CALCULATETABLE(

TOPN(5,

SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])

,Sales[Value], DESC),

Sales[Year]=FORMAT(YEAR(TODAY()),”0000″)),

CALCULATETABLE(

TOPN(5,

SUMMARIZE(Sales,Sales[Year],Sales[Country],Sales[Value])

,Sales[Value], DESC),

Sales[Year]=FORMAT(YEAR(TODAY())-1,”0000″))

)

Now, create a stacked column chart which will give the expected result.

post your comment, if you have any other approaches.

ByHariharan Rajendran

DAX Group by and Filter

This post explains you that how DAX can be used for Group by and Filter logic. To explain this scenario, I am taking an example data.

Example Data:

I have a following table.

EmpID EmpDesignation EmpDepartment DesigFrom
1 A IT – Finance 01/02/2018
1 SA IT – Sales 05/08/2018
2 M Marketing 05/07/2018
2 SM HR 25/08/2018
3 A Sales 06/06/2018

 

Requirement

As per the requirement, I need to perform below actions.

  1. Date Slicer should be available to filter From and To date
  2. Based on the filter, a table of figure should display the latest designation.

Expected Result

Filter – Start Date: 01/02/2018 to 31/08/2018

EmpID EmpDesignation MostEffective
1 SA 05/08/2018
2 SM 25/08/2018
3 A 06/06/2018

 

To achieve the above result, we need to create a DAX script. Use the below DAX script

MosftEffectiveDate = CALCULATE(

MAX(Employee[DesigFrom]),

Filter(Employee, Employee[EmpDesignation] = EARLIER( Employee[EmpDesignation])))

 

Sometimes, you may receive error due to Earlier function. Check the another version of the script without Earlier.

MosftEffectiveDate =

VAR Desig = MAX(Employee[EmpDesignation])

RETURN

CALCULATE(

MAX(Employee[DesigFrom]),

Filter(Employee, Employee[EmpDesignation]=Desig))

 

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