Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Brettp761
Advocate II
Advocate II

Composable DAX Table Functions

CALCULATE() AND CALCULATETABLE() are the only two functions for manipulating filter context (adding to, replacing, or ignoring other filters) so these have to be at the top of my list but it's the functions you pass to them as filter arguments, and sometimes the functions within these functions, that really unleashes the power of DAX.

 

ALL() is often one of these common filter arguments as you want your expression to ignore a filter on a particular column, the entire table, or all columns in the table except a few columns (use ALLEXCEPT() function) .

 

FILTER() is another very common argument function for CALCULATE() that enables you to pass in a 'rich' filter argument such as a measure which isn't allowed by CALCULATE or CALCULATETABLE() alone.

 

So you'll often see expressions like the following:

 

MyMetric:= CALCULATE([MetricA],FILTER(ALL(Product),[MySalesMetric] > 500))

 

In this case we want to modify the filter context applied to Metric A - we want to remove all filters on the Product table via ALL but then filter the rows of this full table (one by one, iterating with FILTER) by a metric (MySalesMetric) such that only rows with over 500 are included. This final, modified Product table is used via relationship to your Fact table (of MetricA) to filter out its rows and then compute MetricA.

 

VALUES() is very important and flexible as well for working with individual columns. Values - Power Pivot Pro

 

The new CROSSFILTER function will be extremely useful for working with complex models. (Bidirectional and CrossFilter White Paper)

 

Variables (VAR) and the new INTERSECT, EXCEPT, and UNION functions will greatly simplify and sometimes improve performance of DAX code as well.

 

 

 

 

 

 

 

 

 

 

 

 

1 REPLY 1
technolog
Super User
Super User

Can you please tell me if the problem is still relevant? If you managed to solve it in some way, please write here how you solved this problem. Then it will be possible to mark this problem as solved.

If not, it may make sense to ask the question again, thus confirming the relevance of this problem.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors