Browsed by
Category: Business Intelligence

Data Dictionary Using DMV Queries

Data Dictionary Using DMV Queries

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 Dynamic visuals using Python in 1 minute

Power BI Dynamic visuals using Python in 1 minute

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

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 Caclulation 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.

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

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 dynamically format a measure by still using a slicer. 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 all the…

Read More Read More

Power BI – Create Custom Interactive Visuals with R

Power BI – Create Custom Interactive Visuals with R

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 > Options and settings > Options>R Scripting: Then set up your R home directories and R IDEs. Set up Node js Open…

Read More Read More

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

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

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

DAX – Exclude data based on Slicer selection

DAX – Exclude data based on Slicer selection

How works a slicer in Power BI? A slicer is just like a filter, it is used to narrow a portion of data shown in a visual.For example if we want to narrow my dataset to only specific regions we just need to tick the region we want to see and all the unticked regions will not show up in our visual. So a normal slicer includes the selected items and excludes the unselected items. But what if instead of…

Read More Read More

Visual level filters for slicers

Visual level filters for slicers

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