New Search

If you are not happy with the results below please do another search

84 search results for: DAX


SSRS Multivalue Parameters in DAX

Déjà vu today with a twist. SSRS multivalue parameters in SSAS reports but this time in DAX. Now that SSAS Designer supports DAX queries, we should be able to do everything we were able to do in MDX, right? Unfortunately, as you will quickly discover, Microsoft “forgot” about multivalue parameters when working on the DAX […]


Tabular DAX Editor

The Tabular toolset is getting better. One thing that I miss from Multidimensional is the cube script that lets you view all custom calculations in one place so that you can organize them any way you want, add comments, etc. This is why I contributed to the DAX Editor tool. Microsoft has taken notice and […]



Now that Power BI Desktop supports Row-level Security (RLS), modelers have a little predicament. On the desktop, the USERNAME() function returns the user’s domain login (domain\login). However, when the model is deployed to, Username() returns the user principal name, which typically (but not always depending on how your AD is set up) is the […]


Aggregates over Aggregates in DAX

Sometimes, you might run into a scenario that requires an aggregate over an aggregate. Consider the following report: The AvgOrderAmount has a simple calculation: SUM(Sales)/SUM(Qty). This calculation is applied uniformly. The Total line would divide 300/7 and will return 42.86. However, what is the user wants the result in the DesiredAvgOrderAmt column which produces a simple […]


Implementing Qlik-style Variables in DAX

A large publicly-traded organization is currently standardizing on Power BI as a single BI platform as a replacement of Qlik and Tableau. They analysts have prepared a gap analysis of Power BI missing features. On the list was QlikSense variables. The idea is simple. The user is presented with a slicer that shows a list […]


DAX Editor Adds Support for Tabular Default Members

In my previous blog, I announced a few new features for DAX Editor for Tabular. Today, I checked in another change that adds support for default members. Currently, Tabular doesn’t have UI for defining default members. However, you can define default members in the MdxScript section of the BIM file using MDX syntax just like […]


DAX Editor New Features

As it stands, Tabular (versions 2012 and 2014) doesn’t have the equivalent of a cube script. Instead, the developer has to use the Measures grid to maintain DAX calculated measures. This is OK with a few measures but as the number of measures grows, the development experience suffers because it’s getting harder to locate these […]


Projecting a Scalar Value with DAX EVALUATE Queries

When you work on more complicated DAX measures, you should get out of the Power Pivot or BISM design environment and use the excellent DAXStudio (or SSMS) with the EVALUATE query syntax. But then eventually the measure would return a scalar value while EVALUATE requires a table. You can use the DAX ROW function to […]


Filtering Tables Dynamically in DAX

Sometimes, your DAX calculated measures might need to filter a table dynamically based on a certain condition. For example, you might have a Type 2 table like this one: ClaimID ClaimDate ClaimStatus RowStartDate RowEndDate XXX-1 1/1/2015 Open 1/1/2015 1/15/2015 XXX-1 1/1/2015 Approved 1/16/2015 12/31/9999 When a change is detected on the existing claim, this design […]


Finding Duplicates in DAX

A prerequisite for creating a relationship in Tabular/Power Pivot is to have a primary key column in the table on the One side of the relationship. This column must have unique values. If it doesn’t, the relationship won’t get created and you’ll get an error that the both tables have duplicate keys. If you have […]