Tag Archives: DAX

ByHariharan Rajendran

How to split by delimiter in Direct Query Power BI Reports?

This article explains you about how to achieve the split function in Direct Query Power BI reports. As you know, we can achieve the split function either in Power Query layer or in DAX but we don’t have a direction function called split in DAX but still we can some other functions which will help us to achieve the result.

Let’s take the below example.

EmpDetail column has Name, Email, Phone number & Department values but are delimited by “|”. We need to split them all and create separate columns.

Split in Power Query

Use “Each occurrence of the delimiter when you have multiple delimiter in your column. It will create multiple columns based on the number of delimiters.

Split function in DAX

We don’t have direct split function in DAX but we have different approaches to achieve the result but we need to create multiple columns as each DAX function is applies to one column.

We can use below functions to achieve the split.

  1. LEN
  2. LEFT
  3. RIGHT
  4. MID
  5. SUBSTITUTE
  6. TRIM

We also can use PATH functions to get the result. Refer below.

Name = PATHITEM(EmpDetails[EmpDetails],1)

Email = PATHITEM(EmpDetails[EmpDetails],2)

Phone = PATHITEM(EmpDetails[EmpDetails],3)

Department = PATHITEM(EmpDetails[EmpDetails],4)

But all the above will work without an issue when we have Import mode connectivity.

What will happen if we use Direct Query?

Let us first try with Power Query first,

If I use Split by column then we will end up with below message.

“This step results in a query that is not supported in DirectQuery mode.”

It means, as we are connecting datasources which are support direct query doesn’t support this Split bu delimiter function so we can’t use this method.

Let us try in DAX layer now. Again, we get same kind of message “Function PATHITEM is not allowed as part of calculated column DAX expressions on DirectQuery models.”

Do we have solution for this type scenario?

Yes. We still can achieve the expected result with the help of other DAX functions which I mentioned above.

Name =

VAR String_Length = LEN(EmpDetails[EmpDetails])

RETURN

TRIM (

        LEFT(

                SUBSTITUTE(EmpDetails[EmpDetails],

                           “|”, REPT(” “,String_Length))

                   ,String_Length

               )

)

The above DAX script will give the name value from the EmpDetail column. We can do the same to split the other values.

Let me explain the above script to understand better.

I have created a variable to store the Length of the whole value. You still can create a new column for length and can be used in this script.

SUBSTITUTE section

SUBSTITUTE(EmpDetails[EmpDetails], “|”, REPT(” “,String_Length))

We are changing the “|” value into spaces. REPT is used to repeat the spaces with length size. The result will be like below.

There are total length spaces between each word.

LEFT Section

LEFT(

          SUBSTITUTE(EmpDetails[EmpDetails],

          “|”, REPT(” “,String_Length))

    ,String_Length

)

Lets assume the length of the whole value is 35, Left function will show the 35 letters from the left position so we will get as like below.

It has extra spaces at the end of each value.

TRIM section

TRIM (

           LEFT(

                     SUBSTITUTE(EmpDetails[EmpDetails],

                   “|”, REPT(” “,String_Length))

          ,String_Length

            )

)

TRIM will remove the extra spaces and show only the proper value. Now, we will get only the Names.

Like the above we need to do the same for other columns, but we can’t use the same formula. We need to modify slightly.

Email =

VAR String_Length = LEN(EmpDetails[EmpDetails])

RETURN

TRIM (

           LEFT(

                   RIGHT(

                                 SUBSTITUTE(EmpDetails[EmpDetails],

                               “|”, REPT(” “,String_Length))

                  ,String_Length*3

                          )

         ,String_Length

         )

)

You need to use RIGHT function and multiply by 3 with the length then use the LEFT function.

Phone =

VAR String_Length = LEN(EmpDetails[EmpDetails])

RETURN

TRIM (

             LEFT(

                        RIGHT(

                                       SUBSTITUTE(EmpDetails[EmpDetails],

                                     “|”, REPT(” “,String_Length))

                    ,String_Length*2

                            )

           ,String_Length

                )

)

Department =

VAR String_Length = LEN(EmpDetails[EmpDetails])

RETURN

TRIM (

    RIGHT(

            SUBSTITUTE(EmpDetails[EmpDetails],

           “|”, REPT(” “,String_Length))

    ,String_Length

            )

)

Result is as like below.

First and Last columns will be easy. If you are working on 2nd column then you need to multiply by “Total columns -1”.

3rd Column “Total columns -2” and so on.

Still there could be other ways. Please share your thoughts.

Happy scripting.

ByHariharan Rajendran

DAX – Daily vs Weekly vs Monthly Measures

This blog post explains, how to use Daily, Weekly & Monthly measures in single chart or table in Power BI. This might be familiar to you, but I keep getting this question about this.

If you want to build a chart only daily or weekly measures then it pretty easy as you can use the date dimension column and take a measure and it will show the daily result when you have proper relationship between the fact and dim – date tables.

When we want to compare daily vs weekly vs monthly, then we end up with using all the measures in single date context. We need to play with CALCULATE to overwrite the filter context for weekly and monthly measures.

First, lets see how to build individual measures. As I said, it is very simple.

I am taking below table for example.

      
Daily Count – Simply take date and count column and create a chart or table in Power BI.

Weekly Count – Simply take Week and count column and create a chart or table in Power BI.

Monthly Count – Simply take monthly and count column and create a chart or table in Power BI.

Now, let us try to merge them all together to achieve like below.

Let me explain to create the measures,
1. Daily Count
2. Weekly Count
3. Monthly Count.
I have created following columns – Year Week & Year Month as we need to create logic to support even for multiple years.
Year Week = Year & Week — 202014
Year Month = Year & Month — 20203

Please use the proper logic to get the correct digits. I left as simple just for demo. In a real-time, you need to handle month single and two digits. Same for week.

Daily Count – No need to build any measure, we can just directly use Date and Count column.
Weekly Count – As we are keeping date column, we need to override the context to get the sum of daily count for that specific week.

Weekly Count = CALCULATE(SUM(TD[Count]),FILTER(all(TD),TD[Year Week] = Max(TD[Year Week])))
TD – is my table name
I have used Year week column.

Monthly Count – this is same as weekly count. Use Year Month column.
Monthly Count = CALCULATE(SUM(TD[Count]),FILTER(all(TD),TD[Year Month] = Max(TD[Year Month])))

Finally, we can combine all the above measures into single visual.

Hope you like this approach. Please share if you find any other alternate options.

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])

12