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
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 – How to Organize Measures in a Metrics Table
Do you have measures spread across multiple tables ? Is it getting hard to find the measures ? Check out this video which talks about organizing measures. https://www.youtube.com/watch?v=w8wPyYatzMo
POWER BI & DAX: How to Flatten Parent Child Hierarchy
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
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
Power Query – Merge Function
Can I use merge function to filter data ? Yes you can and there are gotchas... Listen to the post for details: https://www.youtube.com/watch?v=ycb4qzo0nhE&t=128s
Power Query – Create Functions
How hard is it to create functions in Power Query ? Do I need to learn coding to write functions ? When should I create functions ? I answer these questions in the following post: https://youtu.be/XGlTcgELxG4
