Posts

Power BI DAX Context Transition - Behind the scenes

-
Context transition -  one of the most complex topics to understand and master but have you ever wondered, what actually happens behind the scenes? To be honest at least for me the behind the scene stuff is much more clear and easy to understand. Without diving too deep into theoretical aspect, If I have to explain context transition to a layman I had say take every value of all the columns of the current row in which a measure or an expression inside CALCULATE is called and then convert all the row values into a filter context under which the measure will be evaluated. And this is done for each row of the table where measure is called. And behind the scenes Context Transition it is just like a VLOOKUP between two data cache. Snapshot of the data model (Contoso) used: If you connect your model to DAX Studio and turn on Query Plan and Server Timings and run a simple query like the one below: The result you get is a list of unique Colors with the Sales amount: Now if you navigate
Post a Comment
Read more

Power BI: Visualizing the filter/evaluation context

-
Image
Connect to DAX Studio and Select Define Filter Dump Measure: Copy the code Create a measure and paste the code removing the DEFINE and MEASURE statement Create a blank page and change the detail to Tooltip and add card visual Go to your report and select the visual on which you want to see the filter context, and enable tooltip Hover over the report
Post a Comment
Read more

DAX Studio - Building Queries Manually (Using UI)

-
Image
Since June 2020 a new feature has been introduced in DAX Studio https://daxstudio.org/ that allows you to design queries using the UI, earlier you would have to write code from scratch and this is a great add-on to this tool In this blog I will show you how you can use the interface to create queries yourself. To start with you either need a model inside PBI file, Excel’s PowerPivot or a model deployed in Analysis Services Tabular. First open DAX Studio from start or from PBI/Power Pivot/SSAS and click on Query Builder. Once you click it, it will open the Builder pane. This newly opened pane allows you to drag and drop columns and In this pane you can also add and create new measures. Once you drag columns or measure just click Run Query and Dax Studio will generate result in the result pane If you are like me who likes to investigate the code generated behind the Queries then you can click on “Edit Query” option and it will present the code generated by Query builder. Important Note,
Post a Comment
Read more

Power BI DAX: Calculate Running Total for Past N Days, excluding the days that don't match criteria.

-
Image
Creating a Running Total is pretty simple in DAX, you just take a measure, wrap it inside CALCULATE and then with the help of DATESYTD you can start cumulative total for Dates, Month and one Year ( DATESYTD ) resets at the beginning of new year or any date that you specify in the second argument. Below is an example of a running total using DATESYTD: 1 2 3 4 5 Running Total = CALCULATE ( [Total Sales], DATESYTD ( Dates[ Date ] ) ) One of the problems with DATESYTD is that it does not offer the ability to implement any kind of logic, by logic I mean the ability to filter dates based on the business conditions and then run a cumulative total on it. Fortunately, when you start thinking more in terms of DAX algorithm or the vanilla DAX you start to see that you have a lot of options available, and therefore this post is dedicated to such problems that you have seen or might face in future. Problem Statement:
Post a Comment
Read more