Browsed by
Category: Business Intelligence

DAX measure Top N ranking with drill down
DAX measure Top N ranking with drill down

DAX measure Top N ranking with drill down

Comments 0 Comment

In this short post, we will explore how to create a DAX measure for Top N ranking with drill down. Purpose of this DAX Measure In many business scenarios, understanding the performance of products at different levels of a hierarchy (such as Category, SubCategory, and Product) is crucial. The ‘Top N Ranking with Drill Down’ measure in DAX allows users to dynamically rank products at each level of this hierarchy based on their sales performance. This dynamic capability means that…

Read More Read More

Arithmetic Mean vs. Geometric Mean in Power BI
Arithmetic Mean vs. Geometric Mean in Power BI

Arithmetic Mean vs. Geometric Mean in Power BI

Comments 0 Comment

In this blog post, we will explore the difference between the Arithmetic Mean and the Geometric Mean with some practical examples in Power BI using the built-in DAX function geomean. Arithmetic Mean The arithmetic mean, also known simply as the “mean” or “average,” is the most common measure of central tendency. It is calculated by summing all the values in a data set and then dividing by the number of values. In DAX, you can use the AVERAGE function to…

Read More Read More

Incrementally load data from SQL database to azure data lake using synapse

Incrementally load data from SQL database to azure data lake using synapse

Comments 9 Comments

In this post, we will cover how to create a simple and fully dynamic synapse pipeline to incrementally load data from an SQL database to parquet files stored in a data lake. Why should we move data from a database to a data lake In the traditional data warehouse approach, it is very common to move all the data sources and historize them into an ODS database.However, moving and storing data from all the data sources into a single database…

Read More Read More

Reduce disk space used by Power BI Desktop

Reduce disk space used by Power BI Desktop

Comments 4 Comments

If like your C drive was running out of space and you decided to free up some space you may have realized that Power BI desktop was using a lot of your disk space. In this short post, we will see why Power BI desktop is consuming a lot of disk space and how we can safely reduce it. Analyze disk space usage There are multiple ways to analyze the disk usage space, we can use the built-in windows tool…

Read More Read More

optimize median measure in dax
Optimized median measure in Dax

Optimized median measure in Dax

Comments 1 Comment

In this post, I describe how to write an optimized median measure in Dax which under specific criteria can be 1,000 times faster than the built-in median function.Some time ago I was tasked to migrate a multidimensional cube to a tabular model, this cube had around 2 billion rows and I also had to create a median measure on the new tabular model. I first thought that the median would be much faster to calculate on a tabular model than on…

Read More Read More

Analysis services 2022 distinguish models
Analysis services 2022 new features deep dive part 1: Composite Model and DAX

Analysis services 2022 new features deep dive part 1: Composite Model and DAX

Comments 0 Comment

Since the release of Analysis services 2022 I never took the chance to explore its new features and improvements so in this post I will deep dive into each new and updated feature. The public preview of SQL Server Analysis Services 2022 was released on May 2022 and at the time of writing this post, SSAS 2022 is still in public preview. Analysis Services 2022: Why don’t we just migrate to Azure AS or PBI premium? One might ask if…

Read More Read More

pagination in azure data factory
How to perform pagination in azure data factory using Rest API

How to perform pagination in azure data factory using Rest API

Comments 8 Comments

REST APIs typically have some mechanism to transmit large volumes of records broken up into pages of results. In this post, I describe how to perform pagination in azure data factory or Synapse pipeline using the Azure REST API. What is pagination Azure REST API limits the number of items it returns per result, so when the results are too large to be returned in one response we need need to make multiple calls to the rest API. So when the results…

Read More Read More

Hide tables in Power BI

Hide tables in Power BI

Comments 7 Comments

It is possible to hide tables in Power BI by using the “Hide in report view” option, however, if a user downloads the PBIX file this is still possible to view hidden tables or to unhide tables and this is also true for the reports with a live connection to SSAS or PBI dataset. In this post, I’m going to show another way to hide tables in Power BI which prevents users or anyone to view the hidden tables. Hide…

Read More Read More

Retrieve MDX queries generated by Excel
Retrieve the MDX queries generated by Excel

Retrieve the MDX queries generated by Excel

Comments 4 Comments

When we use Excel to interact with an SSAS tabular model or a Power BI dataset via Analyze in Excel, Excel generates MDX queries which are then sent to the SSAS engine.So whenever a user is complaining about a pivot table connected to an SSAS or PBI dataset being too slow or showing wrong figures we may need to retrieve the MDX queries generated by Excel to investigate the issue. There are several ways to retrieve the MDX queries generated…

Read More Read More

ssas process partitioning
Performance Benefits of Partitioning in Analysis Services Tabular

Performance Benefits of Partitioning in Analysis Services Tabular

Comments 4 Comments

I previously wrote a post on how to dynamically partitioning tables in SSAS tabular using SQL, but in this short post, I will focus on the performance side of partitioning. What is Partition Partitions split a table into logical partition objects. Each partition contains a portion of the data, partitions can be processed in parallel independent of other partitions or excluded from processing operations if they don’t need to be refreshed. Why partitioning improves the refresh performance The main reason…

Read More Read More

Dynamically partitioning tables in SSAS Tabular model using SQL

Dynamically partitioning tables in SSAS Tabular model using SQL

Comments 3 Comments

In this post, I describe how we can dynamically partitioning tables in SSAS tabular using SQL.This approach works well for most of the simple scenarios where we need to keep control over the number of partitions to refresh daily or any frequencies. What is partition Partitions split a table into logical partition objects. Each partition contains a portion of the data, partitions can be processed in parallel independent of other partitions or excluded from processing operations if they don’t need…

Read More Read More

Data Dictionary Using DMV Queries
Data Dictionary Using DMV Queries

Data Dictionary Using DMV Queries

Comments 2 Comments

In this short post, I show how we can integrate a data dictionary using DMV queries within an SSAS tabular model and why we should do so. Why should we have a Data Dictionary within our model? The more complex a model becomes the more documentation we need to have about it. The more measures and complex formulas we have in a model the more calls/questions we have from end-users and thus the less time we can spend on getting…

Read More Read More

Power bi dymaic visuals using python
Power BI Dynamic visuals using Python in 1 minute

Power BI Dynamic visuals using Python in 1 minute

Comments 7 Comments

By now there’s plenty of good tutorials that explain how to create your own visualisations in Power BI by using R or Python like the Microsft one here.However, I haven’t seen any articles on how to create dynamic visuals by using Python (or R).So in this post, I’m going to show a very simple and fast way to create your power bi visuals by using python. (Same can be done using R) Goals of this Post Create visuals by using…

Read More Read More

SSAS/Power BI – Take Calculation Groups to the next level with Tabular Editor

SSAS/Power BI – Take Calculation Groups to the next level with Tabular Editor

Comments 0 Comment

Now it’s been a year since the game-changer new calculation groups feature has been released and I just thought that I still haven’t written a post on it. Goals of this Post Use Calculation Groups Use Tabular Editor Advanced Scripting Speed up SSAS/PowerBI model development Create reusbale script Setting up the Environment Power BI: Download here. Tabular Editor: Download here. SQL Server 2019 and later Analysis Services or Azure Analysis Services. At the time of writing this post, Calculation Group…

Read More Read More

Calculation Group in Power BI – Error: Time Calculation Requires selection of exactly one calculation item.

Calculation Group in Power BI – Error: Time Calculation Requires selection of exactly one calculation item.

Comments 4 Comments

I first thought that the recent calculation group feature wasn’t working in a matrix when selecting more than one calculation item from a slicer, until, I realised that all I had to do to fix it was to simply disable the “column subtotals” option. To be honest I find the error message shown by PBI misleading. So if you encounter the error”Time Calculation Requires selection of exactly one calculation item” here is what you have to do to fix it:…

Read More Read More

Dynamic format strings for currency conversion in PowerBi with DAX

Dynamic format strings for currency conversion in PowerBi with DAX

Comments 2 Comments

I’ve previously written an article on how to dynamically convert and change the currency of a measure using a slicer.Now let’s see how we can use dynamic format strings for currency conversion in PowerBi with DAX. Model Prerequisite Add the FormatString column into your currency dimension You can download the file with the sample format string from here.Make sure to use a Unicode type column to store the different format strings otherwise the collation of your DB might not support…

Read More Read More

Create interactive R custom visual in Power BI

Create interactive R custom visual in Power BI

Comments 1 Comment

In this short post we will see how can step by step create an interactive R custom visual in Power BI Prerequisites Install node.js: Download and Install the latest version of node.js from  the node.js website Install R : Download and install the latest version of R from here.Once R is installed I also recommend you to install R Studio the integrated development environment for R.  Enable R scripting in Power BI: In Power BI desktop, go to File >…

Read More Read More

DAX – TREATAS function – Book-to-Bill Ratio use case

DAX – TREATAS function – Book-to-Bill Ratio use case

Comments 0 Comment

Treatas is a powerful DAX function that applies the result of a table expression as filters to columns from an unrelated table. In other words, treatas applies the filter context of a specific table to another unrelated table (no relationship in the model). Book to Bill ratio A book-to-bill ratio is the ratio of orders received vs orders shipped and billed for a specific period, a month in our case. The book-to-bill ratio reveals how quickly a business fulfils the demand for…

Read More Read More

Power BI Exclude data based on Slicer selection

Power BI Exclude data based on Slicer selection

Comments 0 Comment

In Power BI the slicers allow us to filter and view only the information that we want but we can also in Power BI Exclude data based on Slicer selection. In this short post, I will show how we can reverse the behaviour of a slicer “excluding” the selected values instead of “filtering” the selected values. What is a Power BI Slicer? In Power BI a Slicer is a canvas visual that allows us user to filter and view only…

Read More Read More

Visual level filters for slicers

Visual level filters for slicers

Comments 0 Comment

Since the latest Power BI release (June 2019) we are now able to filter a slicer based on a measure!And I think we’ve all been waiting for this feature for quite a while. One of the top reason we needed this feature was to have the ability to sync slicers. (Well this was already achievable with the bidirectionnel filter which is a really bad practice…) So as we can see on the example below the customer Alma has bought only…

Read More Read More

Visualising Deviation From Average in Power BI

Visualising Deviation From Average in Power BI

Comments 2 Comments

In this post, I illustrate how to implement Deviation From Average in Power BI and why we should use it. Margin is a key metric to assess high-level performance of a company. But sometimes we want to measure and compare specific shop or department or employee performance with the entire company overall performance and this where deviation from average metric comes to help. The requirement are as follows: Company overall margin over time (which is just the margin average) Employee…

Read More Read More

DAX Time Intelligent – Single Measure For Multiple Dates

DAX Time Intelligent – Single Measure For Multiple Dates

Comments 0 Comment

Let’s assume a scenario where we have 50 time intelligent measures and 5 dimension Dates (order, invoice, ship, due, calendar…). If we want the 50 measure to interact with the 5 dimensions we would need to create 250 measures which might rapidly overwhelm the users as they must sift though so many measures. This script below allows you to drag any dates with the YTD margin measure and the measure will dynamically apply the calculation accordingly and thus keeps our…

Read More Read More

SSRS Multi-Value parameter using DAX

SSRS Multi-Value parameter using DAX

Comments 6 Comments

In this post, we will how to set up multi-value parameters in SSRS using DAX. Setting up Multi-Value Parameter using DAX in SSRS with 3 steps For those who are already familiar with SSRS using MDX, it’ll be pretty straightforward as this is exactly the same logic, only the syntax changes and of course the language. In this post, I’m using the AdventureWorks Tabular model database which you can download here 1. Multi-Value Parameter We first need to create a…

Read More Read More

Export Data from Power BI into a file using R

Export Data from Power BI into a file using R

Comments 0 Comment

We usually import Data from file into Power BI, but exporting data from Power BI can be very handy when you want to create a custom visual using R. In fact it can be very cumbersome to code your visual directly into the Power BI script editor. Here are few reasons why you should opt for exporting your Power Bi dataset first and re-import it in R to create your visual. Intellisense is not available in Power BI R script…

Read More Read More

PowerBI – Dynamic Chart Title

PowerBI – Dynamic Chart Title

Comments 1 Comment

Unlike Qlikview, the chart titles in PowerBI can only be static. as you can only pass a static text in the title parameter. However, there’s a way around it! The workaround I found is pretty simple you just need to fake a title by creating a measure that contains your title expression and drop this measure into a Card visual. Then by applying the same transparency and colours of your chart you just need to turn off the chart tile…

Read More Read More

Populating Time Dimension

Populating Time Dimension

Comments 1 Comment

A ready-made script that I have modified to create and populate a Kimball Time dimension. This script will create a time dimension and populate it with different levels of granularity: second, minute, hour.