Category Archives: DAX

Cloud Conference 2016 My video on Power BI + Flow

New behavior in Power BI Desktop

It’s important to understand the different behavior of relationship between current Power BI desktop Engine and previous. I have a table FactInternetSales_LT like this one with a column SalesAmount that contains the amount sold and then ProductKey that contain a primary key of the product. Show in the image below   and i have a Product_LT table  with […]

Variance analysis model with dynamic period in DAX

In this post i want to propose a solution to implement the variance analysis. In this particular exercise I want to have two pivot tables where one show  the variance analysis for product and one for the customer. Also selected period can change and so the quantity are recalculated on the selected period .  Customer A   Customer B […]

DAX Patterns 2015 book available!

 DAX Patterns is a collection of ready-to-use data models and formulas in DAX, which is the programming language of PowerPivot. Create your Excel data model faster by using a DAX pattern!

Free tools for Dax developer

DAX Studio: A tool for running DAX queries against Excel PowerPivot models or Analysis Services Tabular models.  DAX Formatter is a free tool that transform your raw DAX formulas into clean, beautiful and readable code.

Clear cache tabular

Clear cache tabular <ClearCache xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”> <Object> <DatabaseID>Namer</DatabaseID> </Object> </ClearCache>

Better performance with ADDCOLUMNS instead SUMMARIZE

There is three tables: City,Calendar,Pdp. Pdp is the fact tables with 20.000.000 rows. execute the  script below  and analyze the query plan with profiler evaluate summarize( ‘Pdp’, ‘Calendar'[year], ‘City'[City], “Sales”, SUM(‘Pdp'[Price]) ) the profiler shows that VertiPaq engine get years and cities with a scans the fact table if i take the year and the city […]

Shared SalesTerritory with dynamic security

We have a table which contains the sales territory Areas of Sale of a company . We want to share the areas between the two agents.  Furthermore, we want the agent that queries the cube tabular can see only the areas of its competence. First we’re going to go into our database and add a […]

Rewrite the MDX query with the DAX language is better

Today I realize a report with Reporting Services as a data source that used a multidimensional cube in Analysis Services. I wrote an MDX query to query the cube. The query seemed to be made well, but was not performing. I rewrote the query using DAX and I had a better performance. This is because […]

Dax Patterns

A pattern is a general reusable solution to a commonly occurring problem. In Microsoft Excel, you use patterns every day to build tables, charts, reports, dashboards, and more.

Switch to our mobile site