Experience is a great teacher. And in my experience, the beginning has always been tough.

As a BI consultant & trainer, I have worked with various people: from the frontline employees to CEOs. Our interactions gave me great insights into the multiple challenges they face.

I have put together a few pages from my experience and learning; covering Power BI, Excel, Power Query, Power Pivot, Tableau, data analytics & data visualization.

The purpose of the blog is to get you started.

I hope you’ll find them helpful.

Vivek



CALCULATE is the strongest formula in DAX. The filter arguments of CALCULATE can override any existing filter on the same column. For example, the following measure will always return the revenue for the manager Anil irrespective of filters applied by the table visual on the Manager’s column:

1. Revenue = SUMX(Orders,Orders[Unit Price] * Orders[Order Quantity])

1. Revenue Anil =
2.  CALCULATE(
3.  [Revenue], 
4.  Customer[Manager] = "Anil"
5. )













DAX translates the above measure as following:


1.  Revenue Anil = 
2.  CALCULATE(
3.  [Revenue], 
4.  FILTER(
5.      ALL(Customer[Manager]),
6.          Customer[Manager] = "Anil"
7.      )
8. )

It is essentially overriding any external filters applied by the visuals and using its filter (Manager = “Anil”).

Introducing KEEPFILTERS

If we want this measure to show revenue only when the Manager’s name is Anil in the filter context and blanks for the rest of the managers, then we wrap around the filter argument in the above measure in KEEPFILTERS.


1. Revenue Anil KEEPFILTER =
2.  CALCULATE(
3.  [Revenue],
4.      KEEPFILTERS(Customer[Manager] = "Anil")
5. )

Result:


As the name suggests, KEEPFILTERS keeps the existing filter and adds the new filter to the context. It is combining the filters applied by the table visual, and then it further adds the filter of the Manager’s name equals to Anil.


As a result, it returns like an AND condition where it returns a value when it meets both the criteria, otherwise returns blank.

The following measures will help in a better understanding of the KEEPFILTERS:


1. Revenue Anil-Rob KEEPFILTERS = 
2. CALCULATE(
3.  [Revenue],
4.      KEEPFILTERS(Customer[Manager] in {"Anil", "Rob"})
5. )

Output:














It is returning results where both the filter conditions are matching (visual & measure).

It is different from the following measure:


1. Revenue Anil-Rob = 
2. CALCULATE(
3.  [Revenue],
4.      Customer[Manager] in {"Anil", "Rob"}
5. )


The above measure is essentially translated to:


1. Revenue Anil-Rob = 
2. CALCULATE(
3.  [Revenue],
4.  FILTER(
5.      ALL(Customer[Manager]) ,
6.          Customer[Manager] = "Anil" || Customer[Manager] = "Rob"
7.      )
8. )

Use Case KEEPFILTERS

Let us consider creating a measure that returns the revenue of transactions where revenue is greater than 5000. As we do not have any column for revenue in our model, we will use the FILTER function to achieve the objective.


1. Revenue (Large Orders) = 
2. CALCULATE (
3.  [Revenue],
4.  FILTER ( 
5.      ALL(Orders[Unit Price],Orders[Order Quantity]),
6.           [Revenue] > 5000
7.      )
8. )

The above measure will filter the Order table where the Revenue (Unit Price * Order Quantity) is > 5000, and then return the revenue of the filtered table.


Following is the output:












So far, so good.


The challenge comes when we filter the above visual on Order Quantity:
















The output for Revenue (Large Orders) measure remains unchanged, even if we have selected the Order Quantity between 1 & 10. The ALL statement in the measure is overriding any external filters used on the visual:

We can re-write the above measure, and we apply the filter on the entire fact table:


1. Revenue (Large Orders) 2 = 
2. CALCULATE (
3.  [Revenue],
4.  FILTER ( 
5.      Orders,
6.       [Revenue] > 5000
7.      )
8. )

It may look straightforward and give the desired result, but it can have some severe implications on the performance.















The Orders table could be huge, and scanning it row by row to check the condition (Revenue > 5000) can be a time-consuming operation.

KEEPFILTERS to the rescue


We can wrap around our first measure with KEEPFILTERS, which avoids the overwrite of existing filters, and consumes less memory

1. Revenue (Large Orders) KEEPFILTER = 
2. CALCULATE(
3.  [Revenue],
4.      KEEPFILTERS(
5.          FILTER(
6.              ALL(Orders[Order Quantity],Orders[Unit Price]),
7.                  [Revenue] > 5000
8.          )
9.      ) 
10.)


KEEPFILTERS returns a much smaller table for the iteration as compared to the entire Sales table.


Download sample file


@imvivran

#vivran


Power Query is a powerful ETL tool for Excel and Power BI. It has the capability of connecting with multiple data sources and easy-to-use data transformations tools. This article shows three tips to enhance the overall experience.

Rename Applied Steps

For me, Applied Steps under Query Settings is the most crucial aspect of the Power Query table. It is a snapshot of each transformation performed on the table. For each step, Power Query assigns a generic name, which essentially is the name of the applied change.




















By renaming these steps, we can make this segment of Power Query more informative.


How to rename steps?


It is simple – Select the step > Use the key F2

















Alternatively, you can right-click on the steps > Rename

We can add more details using Properties. It provides additional information when we hover on the step.
















Enable Formula Bar

The formula bar is where we can see the M code for each transformation.


We can use it for a few quick modifications and reduce the number of Applied Steps in a query.


In the image below, we are eliminating the steps of renaming a column by modifying the M code in the formula bar:

Apart from this, it also helps in getting familiar with the M query.

To view the formula bar, go to View > Check the option Formula Bar


Using Power Query Formatter

Formula Editor displays the M code for one step. With Advance Editor, we can view all the M codes applied on the table:


Home > Advanced Editor


The M code makes it difficult to read due to the lack of proper structure even with using the Word-wrap feature:


Power Query Formatter provides a neat solution for this. Just copy the entire code from the Advanced Editor, paste it to the PowerQueryformatter.com and format it with one click:


PowerQueryFormatter.com


The output looks neat and more legible.

With a few simple tweaks and modifications, we can enhance the experience of Power Query.



@imvivran

#vivran



Merge Query is a powerful transformation tool in Power Query. It is equivalent to JOINS in SQL. Power Query supports six kinds of joins:


While the most used join kind is Left Join (equivalent to VLOOKUP in Excel), other join kinds are equally useful. This article explores three such use cases.


This article assumes that we know how to apply merge queries in Power Query. For more details, please refer to the article


Use Case 1: Getting only matched records in a table

We have two tables: Orders and Returns.


Download Sample Data file


Order table has all the orders(5497 records), and Returns has order status of 572 orders with status as Returned.


We need one table holding details of returned orders.


With Orders table selected in Power Query, perform Merge Queries as New on Order ID with Returns table, and select Right Outer Join:


The output table holds 572 records of all the orders which have returned.


Use Case 2: Getting non-matched records in a table

The reverse of the Use Case 1: we need a table holding all records for the status of the orders as not returned. This time, we repeat the steps performed earlier with one change: use Left Anti as join kind.


It excludes all the records from the Order table, which are matching from the Returned table.


Use Case 3: Comparing two tables to find differences

Consider the following tables:


The records highlighted in yellow are common in both the tables.

The goal is to find if there is a different Name assigned in the tblB for the same ID.

For this, we perform Merge Queries twice.


Step 1: Merge Queries as New on tblA

In the first step, we Merge queries to find records which are matching on both ID and Name with join kind as Left Anti:


Output: We have four such records that do not have an exact match with tblB on ID & Name.

Remove the column for tblB.


Step 2: Merge Query on ID only on the new table


Now we need the Name from tblB against the corresponding ID; hence we Merge query on ID with Left Outer Join:


Expand the tblB to get the corresponding Name:


In conclusion, there is only one record in tblB where ID is matching with tblA but has a different Name, whereas three records from tblA have no match in tblB.


@imvivran


Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

Join WhatsApp group: BI Simplified

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING