Everything Business Intelligence

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,

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: