Power BI & DAX: How Fast is KEEPFILTERS() Function?

~ Business Intelligist ~ Leave a comment

In my first foray in to investigative journalism, I am taking a look at Microsoft's recommendation to use KEEPFILTERS() function where possible to improve performance. (https://docs.microsoft.com/en-us/power-bi/guidance/dax-avoid-avoid-filter-as-filter-argument) I also talk about Microsoft Power BI Guidance – a very good resource for commonsensical Power BI related advice and best practices. (https://docs.microsoft.com/en-us/power-bi/guidance )

Power BI & DAX Tutorial: How to Implement Allocations

~ Business Intelligist ~ 3 Comments

If you are in FP&A and trying to understand profitability of your products/business units, you have had to face a challenge of allocating certain corporate/HQ expenses. In this tutorial I go over the steps of implementing allocations in Power BI: Desktop file location: https://bit.ly/3dOUTNV The calculations below are used in the tutorial: Rent = CALCULATE ( SUM ( GL[Amount] ), KEEPFILTERS ( GL[Account] = "Rent" ) ) Amount with Allocation = … Continue reading Power BI & DAX Tutorial: How to Implement Allocations

Power BI and DAX: Three Reasons to use DAX Variables

~ Business Intelligist ~ Leave a comment

There are many reasons to use variables in your DAX code. I decided to make a video about the following three: Readability Caching Performance If you find the video to be helpful, you can find the DAX I used in the video below. Net Price = DIVIDE ( Sales[Gross Sales] - Sales[Rebates] - Sales[Discounts], Sales[Units] ) Net Price (var) = --DIVIDE(Sales[Gross Sales] - Sales[Rebates] - Sales[Discounts],Sales[Units]) VAR netSales = Sales[Gross Sales] - Sales[Rebates] - Sales[Discounts] RETURN     DIVIDE ( netSales, Sales[Units] ) Avg Product Price = DIVIDE (     SUMX (         FILTER ( ALL ( Sales ), Sales[Product] = EARLIER ( Sales[Product] ) ),         Sales[Gross Sales] - Sales[Discounts] - Sales[Rebates] … Continue reading Power BI and DAX: Three Reasons to use DAX Variables

PowerBI: Using Power BI Embedded as Dev/Test Environment for Power BI Premium

~ Business Intelligist ~ Leave a comment

Whether "Power BI Pro is a tool for amateurs and Power BI Premium is a tool for pros" is true or not is debatable. What is not debatable, however, is the fact that we need a dev/test environment where we could harden our models without having to affect our production environment. Power BI Premium is … Continue reading PowerBI: Using Power BI Embedded as Dev/Test Environment for Power BI Premium

POWER BI & DAX: How to Flatten Parent Child Hierarchy

~ Business Intelligist ~ Leave a comment

Unfortunately, Power BI does not natively support Parent/Child hierarchies. Luckily, we have several DAX functions that make it possible to convert (or flatten) a parent/child hierarchy into a traditional Power BI Level based hierarchy. In this video I introduce parent child hierarchies, talk about some of the requirements necessary for Power BI to be able … Continue reading POWER BI & DAX: How to Flatten Parent Child Hierarchy

Power BI & DAX: Working with Workdays

~ Business Intelligist ~ 1 Comment

Working with Workdays does not have to be hard work. At least that's the claim I make in the video below: Please watch the video to understand the theory behind the calculations. I am pasting the calcs below just in case you find them useful. DATE Table calcs: IsHoliday =VAR Holiday =    RELATED ( Holidays[Holiday] )RETURN    IF ( Holiday = BLANK (), FALSE (), TRUE () ) WeekDayNo =WEEKDAY ( 'Date'[Date], 2 ) IsWorkDay =IF ( 'Date'[IsHoliday] || 'Date'[WeekDayNo] > 5, FALSE (), TRUE () ) WorkDayID =IF (    'Date'[IsWorkDay],    CALCULATE (        COUNT ( 'Date'[Date] ),        FILTER (            ALL ( 'Date' ),            'Date'[IsWorkDay] = TRUE ()                && 'Date'[Date] <= EARLIER ( 'Date'[Date] )        )    )) Measures: NoOfWorkDays =CALCULATE ( COUNT ( 'Date'[Date] ), 'Date'[IsWorkDay] ) Revenue by Work Day =DIVIDE ( [Revenue], [NoOfWorkDays] ) … Continue reading Power BI & DAX: Working with Workdays