The CALCULATE function in DAX is the magic key for many calculations we can do in DAX. However, it is not pretty intuitive how it works and I spent a lot of time trying to understand how it can be used.

First of all, this is the syntax.

CALCULATE( <expression>, <filter1>, <filter2>… )

The *expression* that we put in the first parameter has to be evaluated to return the result (that is a value, not a table). For this reason, the expression is usually an aggregation function like SUM, MIN, MAX, COUNTROWS and so on.

This expression is evaluated in a context that is modified by the filters in the following parameters. A key point is that these filters can both enlarge and restrict the current context of evaluation. Let’s try to understand what it means by considering a few examples. The following table is the one we import in PowerPivot in a table named Demo.

If we project the count of Rows in a PivotTable putting the Name on the Rows, we get the following result:

Now, we might have the need to calculate a measure which is not affected by the selection of the Name or, in other words, which always calculate the context for all the names. Thus, we define this calculated measure:

CountAllNames = CALCULATE( COUNTROWS('Demo'),

ALL( 'Demo'[Name] ) )

We obtain the CountAllNames column in the PivotTable that always returns the number of all the rows of the Demo table, without considering the filter on the Name.

However, if we add Color attribute to the rows of the PivotTable, the CountAllNames is filtered by that attribute too. In fact, for each color, we have 2 rows for a Name (see Count of Rows column) and 6 rows considering all the names (see CountAllNames column).

Now, we can also add a Boolean expression as a filter of the CALCULATE function. For example, we might filter just the Car transport.

CountAllNamesCar = CALCULATE( COUNTROWS('Demo'),

ALL( 'Demo'[Name] ),

'Demo'[Transport] = "Car" )

In this case, we will reduce the CountAllNamesCar column for a color of Emily to 3, because the number of rows with color Green and Car transport are 3.

At this point we might wonder whether the Calculate filter parameters are enlarging or restricting the context of evaluation. The rule is the following one.

If the current context has a filter on a column of a PowerPivot table (which is a selection of a PivotTable, regardless it is a slicer, a report filter or a row/column selection), any *reference* for that column in one or more filter parameters of the Calculate function replaces the existing context. Then, the filters specified in the CALCULATE parameters are combined together like they were in an AND condition of a WHERE clause of a SQL SELECT statement.

For instance, consider a filter on the Color green using a Boolean expression in the CALCULATE function:

ColorGreen = CALCULATE( COUNTROWS('Demo'),

'Demo'[Color] = "Green" )

**A Boolean expression** used as a filter parameter in a CALCULATE function **corresponds to an equivalent FILTER expression** that operates on all the values of a column (for this reason, you can only a single column can be used in a Boolean expression that is used as a table filter expression):

ColorGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( **ALL('Demo'[Color])**, 'Demo'[Color] = "Green" ) )

We obtain that ColorGreen column always filters by color Green and each Name has only 2 rows with color Green.

Any filter expression in a CALCULATE statement overrides the existing selection of the PivotTable for the columns it contains. In the previous rule we highlighted the “reference” definition, because the FILTER that is internally used in place of the Boolean expression uses a FILTER expression that returns a set of values for the Color column. Thus, the existing selection for the color (the color is in fact specified in the rows of the PivotTable) is overridden by our filter and only green rows in the source table are considered to calculate the ColorGreen measure value. The true reason we lose the current selection on the color attribute is that the ALL( Demo[Color] ) expression returns a set of all the color values and ignores the existing selection.

If we don’t want to lose the existing selection of the PivotTable (that means that we don’t want to lose the existing filters on the calculation context), we can simply use in the FILTER expression a function that doesn’t ignore the existing selection. Instead of using the ALL( Demo[Color] ) expression as the source of the filter, we can use the VALUES( Demo[Color] ) expression, which keeps existing selections and returns the values still available in the color attribute.

In fact, if we use the following calculated measure:

ColorGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( **VALUES('Demo'[Color])**, 'Demo'[Color] = "Green" ) )

We obtain as a result that the Color filter in the PivotTable is still active, and returns no rows for all the colors but green.

We can see that the FILTER expression in a CALCULATE function *always replaces* the previous context for the referenced columns. However, we can save the existing context by using an expression which uses the existing context and further restricts the members we want to consider for one or more columns. And this is what we have done using VALUES formula instead of ALL as the first parameter of the FILTER call.

Thanks to Marius Dumitru, the various combination of FILTER, ALL, VALUES in a CALCULATE statement can be summarized in this way.

= CALCULATE(

COUNTROWS('Demo'),

FILTER( **ALL('Demo'[Color])**, 'Demo'[Color] = "Green" ) )

-- ignores/replaces existing Color filters and sets a filter on Green

= CALCULATE(

COUNTROWS('Demo'),

FILTER( **VALUES('Demo'[Color])**, 'Demo'[Color] = "Green" ) )

-- keeps existing Color filters and adds a further filter on Green

= CALCULATE(

COUNTROWS('Demo'),

FILTER( **ALL('Demo'[Color])**, 'Demo'[Color] = "Green" ),

** VALUES('Demo'[Color])** )

-- same as the previous expression (keeps existing Color filters)

-- NOTE: the first filter would consider all the colors, but the second

-- expression (VALUES) only consider the current selection and

-- the two filters will be considered using an AND condition, thus

= CALCULATE(

COUNTROWS('Demo'),

FILTER( **ALL('Demo')**, 'Demo'[Color] = "Green" ) )

-- ignores/replaces filters on all Demo columns,

-- and sets a filter on **all** columns (Name, Transport etc., not just Color)

-- with rows that meet the condition

Finally, some words of caution have to be spent to the first parameter we pass to the FILTER function. If we consider this expression:

ColorGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( **'Demo'**, 'Demo'[Color] = "Green" ) )

We pass the whole Demo table to the FILTER condition, which results a filter of the current context with **all the columns**! In this way we apply a restrictions on the color green and we get the same result as before (no rows for all the colors but green, the selection of color of the PivotTable is still applied) but, remember, the FILTER is returning ALL the rows. What does it mean?

Well, consider a further selection on the PivotTable where the Transportation attribute is filtered by Bike. This is the result using the ColorGreen definition we have just defined.

Now, let’s add another filter to the CALCULATE function, filtering also the rows with Trasport equals to Car.

CarGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( 'Demo', 'Demo'[Color] = "Green"),

**'Demo'[Transport] = "Car"** )

which, as we have seen before, corresponds to:

CarGreen = CALCULATE(

COUNTROWS('Demo'),

FILTER( 'Demo', 'Demo'[Color] = "Green"),

**FILTER( ALL( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" )** )

The results is that the filter for Bike defined in the PivotTable plus the filter for Car defined in the CarGreen measure returns no rows at all in the CarGreen result. If we think about it, why this happens is not very intuitive at this point!

Our filter on the Transport column is actually restricting the current selection and is not replacing it! But, wait, if we write just the filter on Transport, without the filter on Color, we have:

TransportCar = CALCULATE( COUNTROWS('Demo'),

**'Demo'[Transport] = "Car"** )

that produces the following result, which replaces the Bike selection of the PivotTable in the TransportCar column!

This last calculation (TransportCar) is simple to explain, because it has the same behavior we have seen before with the first ColorGreen calculated measure, where the filter of the color green replaced any existing color selection in the PivotTable. The difference in the CarGreen calculation is the other filter parameter, which returns all the columns from the Demo table. Consider the highlighted filter in the CarGreen formula:

CarGreen = CALCULATE(

COUNTROWS('Demo'),

**FILTER( 'Demo', 'Demo'[Color] = "Green")**,

FILTER( ALL( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" ) )

The filter on color green returns *all the columns* of the current context. If we consider the corresponding rows for the cell B5 of the PivotTable (Emily, Green, Bike), this is just one row (the yellow one), and this row has the Bike value for the Transport attribute. When we apply the second filter, we have a single value for the attribute Transport, which is Car. At this point, the intersection between those two sets of Transport (one is only Bike, the other is only Car) is an empty set. Thus, the result for CarGreen measure is empty, because there are no corresponding rows for the selection made.

This can be tricky, but we finally have this behavior.

- The CALCULATE function applies a calculation (the first parameter) for each cell, considering the resulting context by applying the filters (the second and following parameters) to the current context.
- Each filter can have values for one or more columns.
- Each column is computed individually in the filters expressions of the CALCULATE function
- If a column value is specified in at least one filter, it replaces the selection of the current context for that column.
- If a filter expression returns more columns, each one has its own independent set of values in the final calculation context definition
- If a column is specified in more filters, the resulting values are the intersection of these set of values (for that column).
- After all the filters have been evaluated, the intersection of the column values determines the calculation context for the expression passed as the first parameter to the CALCULATE function

Despite its complexity, this calculation is pretty fast. The key point is to understand all the side effects we have when a filter returns more columns than those we specified in the filter condition itself, which is something we have to consider carefully each time we use one or more FILTER functions inside a CALCULATE expression.

## Comments

## Kasper de Jonge said:

Great post Marco, keep m comming :)

## SQLBI - Marco Russo said:

PowerPivot supports only one type of relationship between two tables, which is the one-to-many relationship.

## sam said:

Is there a way to add a measure that will show a 1 against the first instance of a particular name or name color combo

So in your example

Name Flag

Emily 1

Kimberly 1

Emily 0

Victoria 1

Kimberly 0

Victoria 0

Emily 0

etc

## Marco Russo (SQLBI) said:

Sam,

the problem here is what is the definition of "first instance". Probably, you want to refer to the physical order of data. Can you be more explicit? Do we have some constraint to look for a solution?

Marco

## SQLBI - Marco Russo said:

When you use CALCULATE in DAX you are creating a new filter context for the calculation, based on the

## Martijn Muilwijk said:

Marco,

Is there any scenario you can think of in which you would prefer using the FILTER() statement over the CALCULATE() statement? In other words: It is always possible to achieve the same functionality with the help of CALCULATE() when using FILTER()

## Marco Russo (SQLBI) said:

Usually no, but FILTER has to be used when you have to pass a parameter to CALCULATE.

In fact:

CALCULATE( <expression>, <filter1>, ... <FilterN> )

every <filter> parameter is often the result (implicit or explicit) of a FILTER statement.

## Craig said:

Just want to say "thank you" for the great article after many hours of frustration.. with your help ive finally worked out the filter function:) next step.. buying the book!

## Max said:

Hi Marco,

Can you suggest a clean way to use Calculate with a conditional filter. This was easy in MDX using SCOPE but I've not yet figured it out in DAX.

Say for example you want a total for every year except for the current year you want to use the previous year total.

MDX

CREATE MEMBER CURRENTCUBE.[Measures].[Best Estimate Claims] AS ([Measures].[Claims]), VISIBLE = 1 ;

SCOPE ([Measures].[Best Estimate Claims],[Year Of Account].[Year Of Account].&[2012]);

THIS = ([Year Of Account].[Year Of Account].PrevMember,[Measures].[Claims]);

DAX

[Best Estimate Claims] = CALCULATE(SUM(DFR[Claims]), PARALLELPERIOD(YearOfAccount[FirstDayOfYear],IF(MIN(DFR[YOA])=2012,-1,0),YEAR),ALL(YearOfAccount))

(A relationaship exists between DFR[YOA] and YearOfAccount[YearOfAccount])

The two expressions above are equivalent in results but the DAX feels like a bodge

## Marco Russo (SQLBI) said:

You don't have something like SCOPE in DAX - you might write the same DAX expression in different ways, for example by moving the IF before the CALCULATE, but at the end it would not change that much.

MDX is more advanced, DAX is simpler (even if powerful) and has a different approach.

## Frank Tonsen said:

Hi Marco,

with regard to your book (pages 196-198)

maybe I am wrong, but IMO

CALCULATE(SUM(Orders[Quantity]),Orders[Color]="Green")

overrides color context

CALCULATE(SUM(Orders[Quantity]),VALUES(Orders[Color]),Orders[Color]= "Green")

keeps color context, or to be more precise kills color context and then restores it

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

keeps all context, or to be more precise kills all context BUT THEN ALSO RESTORES IT.

THEREFORE, naturally the measure

CALCULATE(SUM(Orders[Quantity]),Orders[Color],Orders[Color]="Green" ,Orders[Size]="Small") returns no rows at all if a filter for 'Large' is defined in a slicer.

## Frank Tonsen said:

Unfortunately I made a typing error in the last paragraph, of course the second parameter of the CALCULATE function has to be Orders not Orders[Color]:

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green" ,Orders[Size]="Small") returns no rows at all if a filter for 'Large' is defined in a slicer.

## Marco Russo (SQLBI) said:

Frank,

the parameters you put in a CALCULATE call are all put in an AND condition, so

CALCULATE(SUM(Orders[Quantity]),VALUES(Orders[Color]),Orders[Color]= "Green")

replaces existing filter on Color, but the two parameters will be put in AND together - so at the and the filter on Color has now values if the existing selection didn't include Green, or is just Green if existing selection included Green.

In case of

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

you have the same result, but in this case Orders is like VALUES( Orders ), and gets all the rows selected in Orders instead of just a selection for one column - so Green filter always wins.

The last expression

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green" ,Orders[Size]="Small")

returns no rows at all if you have a filter on Large, because the Orders parameter contains the current selection of Orders rows, which are only those Large (according to slicer selection) and the filter on Size equal to Small produces an empty result.

Sounds good?

## Frank Tonsen said:

Marco,

thanks for your quick answer. I guess it confirms my understanding.

Thus, regarding CALCULATE there is actually no point in using the facts table as a parameter because

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

is the same as

CALCULATE(SUM(Orders[Quantity]), Orders[Color]="Green")

Right?

## Marco Russo (SQLBI) said:

No, they are not the same.

If you have

CALCULATE(SUM(Orders[Quantity]), Orders[Color]="Green")

and you had a selection of Color = "Red" in the PivotTable, such a selection is replaced for this formula with Green and you can evaluate a different set of rows in Orders.

When you have:

CALCULATE(SUM(Orders[Quantity]), Orders, Orders[Color]="Green")

then the initial selection is "copied" in Orders, and at this point the replacement of the Color selection with Green is still not enought to go outside of the current filter context, because the copy of Orders in the parameters of the CALCULATE makes a copy of the initial selection (i.e. Red) translated to the Orders table, and the result is an empty set in this case.

Marco

## Frank Tonsen said:

Marco,

now I got it.

Many thanks for your explanations.

## Gunshy said:

Hi,

Need help.

How can I conditionally calculate a table? for example, If Table[SourceType]="SALES" then SUM(Table[Quantity]) else CALCULATE(Table[Quantity],DATESBETWEEN(DateTable[Date],DATE(1900,1,1),LASTDATE(DateTable[Date]))) ?

Thanks

## Marco Russo (SQLBI) said:

Gunshy,

if you mean that you want to use the selection made by the user, you should use something like:

IF (

HASONEVALUE ( Table[SourceType] ),

IF (

VALUES ( Table[SourceType] ) = "SALES",

SUM( Table[Quantity] ),

CALCULATE(

Table[Quantity],

DATESBETWEEN(

DateTable[Date],

DATE(1900,1,1),

LASTDATE(DateTable[Date])

)

)

)

)

## Oleg G said:

Wow! I just spent several hours re-creating this blog entry in Excel, and between this and last week's PowerPivot Workshop, I think I finally got it! Now it suddenly seems very easy and intuitive!

## Marco Russo (SQLBI) said:

After a few years, I still think it is not intuitive! But it can be easy after you get used to it. :-)

## Leshi said:

Hi Marco,

If I want to filter on multiple values such as Product ID in (123,456), how can I use IN statment in my filetr within CALCULATE measure...i.e. I am trying to acomplish something like this

CALCULATE(SUM(SALES),PRODUCT_ID=123,456) ...Is there an IN clause or something similar?

## Marco Russo (SQLBI) said:

Leshi, unfortunately there is no IN statement in DAX. If you store the list of products in another table, you might use the CONTAINS function in DAX.

## Leshi said:

Thanks Marco,

After tinkering around a little, I realized I could use an OR statement (||) if one to filter by few values instead...

Thanks for your feedback and keep up the good work

Leshi

## Marco Russo (SQLBI) said:

Oh yes, the OR statement is good too, it's just longer to write if you have many items to check! :)

## BI Analyst said:

Hi Marco,

Thank you for your tutorial. I have a question regarding the example toward the end of this tutorial.

In

CarGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( 'Demo', 'Demo'[Color] = "Green"),

'Demo'[Transport] = "Car" )

The measure returns no rows in the pivot table.

My understanding (or logic) is that because a transport cannot be a bike and a car at the same time.

But in your next example which you removed the Color filter

TransportCar = CALCULATE( COUNTROWS('Demo'),

'Demo'[Transport] = "Car" )

My logic is no longer holding because the measure column in the pivot table is not empty.

Could you help to explain (in the simplest way possible) why the one with Color filter return empty values and the one without Color filter return non-empty values?

Thanks again in advanced!

BI Analyst

## Henk Hofmans said:

I have got two tables with a commen key field. The tables cannot be related because there are duplicate values on both sides. In table "A" I create a new column that should count the number of occurences of "A.key" in the "key" column of table "B".

This is what I have tried, but doesn't work:

=calculate(COUNTA(A[Key]);B[Key]=A[Key])

How to solve this?

## Marco Russo (SQLBI) said:

You should write this:

= CALCULATE ( COUNTROWS ( A ), FILTER ( ALL ( A[key] ), A[key] = B[key] ) )

## Luke said:

Great post - very useful.

Does anyone know if there is a way of removing the current filter of a pivot table, but still keeping the row filters? For example, I'm trying to be able to filter a pivot table down on a particular group, but have one average across all the data with the same row splits.

Cheers

## Luke said:

Looks like the answer to my question is fairly straightforward - for ignoring the filter on a Pivot Table, but keeping the row/column filters you can just use CALCULATE(Expression,ALL(FilterColumn)

## Anand said:

Great Man. That is why I am Ur fan Marco.

I was trying to buy ur book on Data Modelling in Powerpivot for excel 2013.

But I was unable to get the e-Version.

## Marco Russo (SQLBI) said:

You can get the Kindle version on Amazon or other formats on oreilly web site. Let me know where you looked at.

## Rekha said:

Hi Marco,

I have 7 tables and each one is having unique column.e.g.I have table A with NRIC column as unique.Other table as B having Investigation ID as unique column with NRIC as a foreign key. All tables are some what related to each other.

When I try to create pivot table I come across the below scenario:

when I drap NRIC from table A and want to display related Investigation ID from table B, it shows me incorrect result.I mean one NRIC gets associated with all Investigation IDs.

I am new to Power Pivot Table.Please suggest how should I go about this.

Thanks

## Marco Russo (SQLBI) said:

Rekha,

from your description, it seems that you didn't create relationship between tables in the data model. Go in the Diagram View of the Power Pivot window and check that relationships are correctly defined between tables.

Marco

## Rekha said:

Thank for quick reply Marco.

I have created relationships between tables, but result is not as per our expectations.

For each NRIC from table A, it should return only those associated Investigation Ids from table B.

Am I missing anyhting?

## Marco Russo (SQLBI) said:

Rekha,

have you put a measure in the pivot table? If you don't have a measure, hierarchical relationships between different tables are not automatically identified by the Pivot Table in Excel.

Marco

## Rekha said:

Can you help me in this, as I am completely new to this?

Thanks

## Marco Russo (SQLBI) said:

Rekha,

I suggest you looking at the help or some presentation - look at how you create a pivot table starting from a model with more than one table. You can also look at some of our books:

http://www.sqlbi.com/books/

## Rekha said:

I was looking for some samples.

Thank you so much Marco for your help.

## Malcolm said:

Can calculate function together with a Contain function. eg. Calculate(sum(table1),contain(table1,col1,"ast_")).

I have purchased the the book Microsoft Excel 2013 - Building Data Models with PowerPivot written by you and couldnt find a solution in that

Thanks

## Marco Russo (SQLBI) said:

It's not clear what you want to do. Contains is a sort of "lookup", which might be not what you want to do - it seems that you want to do just this:

CALCULATE ( SUM ( table1[col2] ), table1[col1] = "ast_" )

But if you want to do a sort of "like" statement, then you have to write:

CALCULATE ( SUM ( table1[col2] ), SEARCH ( "ast*", table1[col1], 1, 0 ) > 0 )

Check this article for more info about string comparison in DAX:

http://www.sqlbi.com/articles/from-sql-to-dax-string-comparison

## Malcolm said:

Yes I wanted a like statement and got hold of your thread after I left a comment. But still could'nt solve it. Figured out the reason to be the * mark. Thanks Marco.

## Rohit said:

Hi,

I have a table with 10 columns. One of the column has values. I want to add another column where it will add the values with a filter condition on another column. EX: i have 10 rows and 2 columns, one having numerical values and other dimension data as shown below

x | y | New Column(Get values from x column where Y = 'A')

1 | A | 1

2 | A | 1

3 | A | 1

4 | B | 0

5 | B | 0

6 | B | 0

7 | C | 0

8 | C | 0

9 | A | 1

10 | A | 1

Can anyone help me understanding how to apply the filter and get the values in DAX.

Thanks in advance.

## Marco Russo (SQLBI) said:

Rohit, it's not clear whether you want to obtain this in a PivotTable or in a Power Pivot table. For the latter, just create a calculated column with the expression [x] = "A"

For a PivotTable you can create a measure defined with

IF ( HASONEVALUE ( table[x] ), table[x] = "A" )

## ramiro said:

marco, i have a problem, i have two measures, and then i made a calculation substraction, and i get positive numbers and negative numbers, with this result i want only sum negative numbers but i dont know what formula type, you can helpme??

I appreciate anything help.

I talk about power pivot.

## Marco Russo (SQLBI) said:

Ramiro, sorry, I don't understand the question...

## ramiro said:

Marco, sorry.

I put the example:

Product Sales Budget Dif

A 1 1 0

B 2 2 0

C 1 0 1

D 3 5 -2

Total 7 8 -1

I need separate the negative from Dif and then sum it. But the result i get on Total is always the difference not the sum.

Product Sales Budget Dif Only negatives

A 1 1 0 0

B 2 2 0 0

C 1 0 1 0

D 3 5 -2 -2

Total 7 8 -1 -1

Only negatives, i calculate: =IF(Dif<0,Dif,0) But on the Total always give me de the difference not the sum. That i want is sum all negatives number only when i separate on column Only negatives.

I hope you understand me now, I am from Mexico and I speak English a Little.

Thanks a lot, anyway.

## Marco Russo (SQLBI) said:

Create a measure such as:

CALCULATE ( SUM ( table[dif] ), table[dif] < 0 )

Marco

## ramiro said:

I forgot, but (Sales) and (Budget) are already a measures, and (Dif) is a measure of (Sales) minus (Budget) and (Only negatives) is a measures for (Dif).

If I do what you say, this appear:

Error de cálculo en la medida 'INVENTORY_TRANS'[A]: Se ha usado una función 'CALCULATE' en una expresión True/False que se utiliza como expresión de filtro de tabla, lo que no está permitido.

## ramiro said:

OH! SORRY, THANKS FOR THE ANSWER.

## Anand said:

Marco , any Idea How Can I change "CarGreen" to "CarRed" ie

"Car"& AnyVariable so that I can Dynamically change the column

Name.

At last Once again for proving an Excellent book on PowerPivot for

SQL & VBA coders like us.

## Marco Russo (SQLBI) said:

Anand, you cannot dynamically rename column names. However, I would suggest you having a column "Car" and Green / Red / ... as values in that column. Then, you can create a measure and split it by values in Car attribute.

## Kumar said:

Marco, I have one problem in one of measure creation in Tabular model. I have to create a measure which should display value for only monthly. It should not give null value for Yearly, Quarterly. I have a Date Dimension which has Year, Quarter and Month values. How can I achieve this

## Marco Russo (SQLBI) said:

Kumar, take a look at the ISFILTERED function to check what the user is selecting. I'm not sure I understand what you want to obtain when Year and Quarter are selected.

## Kumar said:

You’re Champ Marco, ISFILTERED function worked to me. This is unique business scenario, where we have to show the data for monthly rest all we need to show blank value. Thanks for your suggestion.

## Ron Jonas said:

Marco - I'm hoping you can help... This question pertains to "Calculated Items" or what used to be calculated items before PowerPivot.... I have the need to calculate the difference between items within the same data column. To try and keep it simple, let's say I have 3 columns of data; Component, Version and Amount. We'll use Bookings as the Component item, Archive and Active as Version items and amounts. What I want to do is to calculate the difference between Archive and Active within the Bookings component.

The data table would show:

Component Version Amount

Bookings Archive 1000

Bookings Active 950

In my pivot, I want to show the Archive and Active amounts and have the pivot table calculate the difference... When I try and use calculate(sum), it doesn't like it... :-)

Any thoughts?

Much appreciated. Ron

## Marco Russo (SQLBI) said:

Ron, you should create a measure (calculated field in Excel 2013) by using simply these expressions:

ArchiveAmount := CALCULATE ( SUM ( table[amount] ), table[version] = "Archive" )

ActiveAmount := CALCULATE ( SUM ( table[amount] ), table[version] = "Active" )

DifferenceAmount := [ArchiveAmount] - [ActiveAmount]

## Andy said:

Hi,

so, do I understand correctly that there is no easy way of calculating the GRAND TOTAL by using a formula which takes existing filters into consideration?

Every pivot table is giving me a GRAND TOTAL at the bottom based on various filters (on slicers or the pivotfilter). But some smart DAX developer guy is not able to program an expression that returns the same value? I am thinking very easy like =GRANDTOTAL([column1]) :)

If I change a filter, I always have to change my measure manually?

I am searching for days now and I am refusing to believe that there is no easy and flexible way.

Cheers,

Andy

## Marco Russo (SQLBI) said:

Andy, I don't understand what you mean. When you define a measure, it's automatically calculated in the Grand Total by the Pivot Table in Excel, which generates an MDX query.

If you need to obtain the grand total in a pure DAX query (assuming you're directly querying the model without using a pivot table), you should use ROLLUP in the SUMMARIZE statement.

## Ron Jonas said:

Marco - You.ROCK... Thank you so much... It worked like a charm!!

## Andy said:

Hi,

My target is to create a measure TotalNetSales per each year.

I have the following PowerPivot and with the DAX:

=CALCULATE(SUM(Products[NetSales]),ALLSELECTED())

I get this:

2013 2014

NetSales GP TotalNS NetSales GP TotalNS

$100.00 $50.00 $7,800.00 $200.00 $50.00 $7,800.00

$1,000.00 $300.00 $7,800.00 $2,000.00 $300.00 $7,800.00

$200.00 $60.00 $7,800.00 $400.00 $60.00 $7,800.00

$300.00 $70.00 $7,800.00 $300.00 $70.00 $7,800.00

$400.00 $40.00 $7,800.00 $500.00 $40.00 $7,800.00

$500.00 $30.00 $7,800.00 $700.00 $30.00 $7,800.00

$600.00 $20.00 $7,800.00 $600.00 $20.00 $7,800.00

$3,100.00 $570.00 $7,800.00 $4,700.00 $570.00 $7,800.00

But what I want is this:

2013 2014

NetSales GP TotalNS NetSales GP TotalNS

$100.00 $50.00 $3,100.00 $200.00 $50.00 $4,700.00

$1,000.00 $300.00 $3,100.00 $2,000.00 $300.00 $4,700.00

$200.00 $60.00 $3,100.00 $400.00 $60.00 $4,700.00

$300.00 $70.00 $3,100.00 $300.00 $70.00 $4,700.00

$400.00 $40.00 $3,100.00 $500.00 $40.00 $4,700.00

$500.00 $30.00 $3,100.00 $700.00 $30.00 $4,700.00

$600.00 $20.00 $3,100.00 $600.00 $20.00 $4,700.00

$3,100.00 $570.00 $3,100.00 $4,700.00 $570.00 $4,700.00

I have tried to do it with a SUMMARIZE statement and ROLLUP as you suggested, but I cant get it to work.

Thanks for any help,

Andy

## Andy said:

oops, that doesnt look good anymore :)

The difference between these Pivots is, that for the first Total it gives me the sum of both TotalNS for year 2013 + 2014 (3100 + 4700 = 7800). But I need the TotalSum for each year separately.

Shame I cant insert a picture.

Sorry again. Andy

## ZT said:

Hi Marco

I get one row for every Name when I write the code from your exampel in Powerpivot 2013

CarGreen = CALCULATE( COUNTROWS('Demo'),

FILTER( 'Demo', 'Demo'[Color] = "Green"),

'Demo'[Transport] = "Car" )

Or

CarGreen = CALCULATE(

COUNTROWS('Demo'),

FILTER( 'Demo', 'Demo'[Color] = "Green"),

FILTER( ALL( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" ) )

Why is that?

## ZT said:

Ah I see now.. I didnt applied the filter on the slicer.

## Roy said:

Hi Marco,

I have a measure that employs the sum of multiple calculates, because I need to do a lot of aggregations, one for each cost component. But formula is too slow, do you know if there is an easier way?

## Marco Russo (SQLBI) said:

Roy, the problem might depend on many conditions. You should analyze the query plan (see http://www.sqlbi.com/articles/understanding-dax-query-plans/ for information about how to do that).

## Hady akl said:

Dear Mark

how are you,

i have a question please,

access type column 1

Change tape <blanc>

Change tape <blanc>

Maintenance MA0005

Maintenance MA0005

Check CH0007

Check CH0009

Check CH0013

Check CH0013

i just wrote a DAX: Calculate(countblank(Table1[column1])+calculate (distincount(table1[column1])) so i get a formula named ABC.

I put

access type in rows

month in column

ABC formula in values

got this table:

Logs Months

Access Type January February March April Grand Total

Check 11 18 13 6 48

Maintenance 8 10 5 7 30

Tape change 244 247 297 243 1028

Grand Total 263 275 315 255 1105

by default powerpivot give me the grand total column my question is and after many formula tries how can i write the grand total as formula in order to calculate percentage later on

thank you

## Anthony Newell said:

Hi Marco, hope you are well

I've just had a too-good-to-be-true moment

I have a Costs table and a Sales table

In my Sales table, I've created a measure 'Reduced Sales' - products sold which have been reduced in price

In my Costs table I wish to create a measure called Net Waste. This will be equal to gross waste (found in my cost table) less Sales revenue from Reduced Sales

My Net Waste measure will be utilised in a specific column, so it will take on a row context for a specific store ID

So effectively, If I wish to calculate net waste for a specific store I want filter context to apply in both Sales and Costs tables based on a Store ID

I've created the following:

1) Reduced Sales Measure in Sales Table:

SA_YTD_TY_ACT_DEPT:=calculate([SA_YTD],filter(FactSales,FactSales[Sales_datadesc]="TY_ACT_DEPT"))

2) Net Waste Measure in Costs Table:

CA_YTD_TY_ACT_REDUCED_SALES:=calculate([SA_YTD_TY_ACT_DEPT],FactCosts)

You can see that in the latter I'm passing the calculate function a measure from one table and an entirely different table in it's entirety being costs

If I take CA_YTD_TY_ACT_REDUCED_SALES and put it in a calculated column '=[CA_YTD_TY_ACT_REDUCED_SALES]' then I get a net waste calculation for every single store simultaneously applying filter context in two separate tables

I was scratching my head wondering how to tackle this but it appears to work!?!

## Anthony Newell said:

You'll also see from the above that I'm passing pre-defined measures e.g[SA_YTD] into the expression argument of the calculated function

These perform aggregations and allow me to build my calculations up step by step providing a trail for the less experienced to follow

I wasn't sure if you could pass a measure as an expression in a calculate function but providing it performs some kind of aggregation then the answer would appear to be yes

## Marco Russo (SQLBI) said:

@Hady - sorry but I don't understand the question! Maybe you want to use the "display as a percentage" feature in Excel pivot table?

@Anthony: short answer: you are affected by the context transition - when you have a row context and you call a measure, you have an implicit CALCULATE statement wrapping the measure expression that transforms the row context into a filter context. Long answer: this is explained in our Power Pivot, BISM Tabular and DAX books! :-)

## navision user cz said:

Hello,

I am struggling with the following formula. it probably never evaluates and almost always freezes the program. It probably could and should be improved to actually work.

=calculate(DISTINCTCOUNT(q[Document No_]);filter(q;q[zleva]="1PHM"&&q[Sell-to Customer No_]=earlier(q[Sell-to Customer No_])))

q is a table containing the lines of rows of all invoices.

sell to customers no = customer identification.

document no = invoice identification.

q[zleva]="1PHM"&& identification of the product category I am interested in

q has many millions of rows and both customer no and document no are repeated many times.

With the formula I am trying to calculate for every line, how many invoices that customers has that have the "1PHM" good in them.

Eventuall goal is to mark the customers who bought that item certain amount of times, but this need to be a line information, since I will be evaluating this information in other contexts

in sql this would be something like:

select customer, count(invoices)

from q

where line text = PHM

group by customer

having invoice >3

## Dean said:

Marco, great post and great site.

I have filtered results in a power pivot table that not only do I need to compare, but I need to be able to use the comparison in later calcluted measures. For example, I send a catalog to a list and withhold a control group to later calculate how much "lift" the catalog provided above what would have happened if nothing was done. In a single column ("[Control_Group]"), each recipient is flagged if they are in the control group ("control"). Recipients not in the control have a blank value in the control group column.

DAX formulas makes it easy to summarize response rate, revenue, AOV, etc. for each group. What I am not able to do is find a DAX formula that can calculate the lift; that is, the difference in the amount of revenue each group generated per member. Revenue per member, yes. But not the difference. At least not as a calculated measure that allows me to use it in additional summary calculations.

The pivot table would look like this:

Catalog Count Orders Revenue AOV Revenue_per_person Lift* Total_Lift* Cost_of_Catalog Net_Profit*

Blank 1000 90 9000 100 9 1 1000 600 400

control 100 10 800 80 8 0 0 0 800

Each asterisked measure relies on being able to reference the difference between the values under Revenue_per_person.

I have tried something like this: Lift:=Revenue_per_person - CALCULATE(Revenue/Count,ALL(Customer[Control_Group],FILTER(Customer,Customer[Control_Group]="control"))

But row context in the "Blank" row already has no rows with "control group" in the [Control_Group] column, so the result is zero.

Instead of the ALL command, what I need is the ability to ignore that one row context ("Blank()") and substitute it with another row context ("control group"). How can I do that?

Or do I need to take another approach?

## Marco Russo (SQLBI) said:

@Navision user cz: try this

FILTER (

ADDCOLUMNS (

Customer,

"Invoices", CALCULATE (

DISTINCTCOUNT ( q[Document No_] ),

q[zleva]="1PHM"

)

),

[Invoices] > 3

)

@Dean: sorry but without a study of the data model and the DAX formulas it's hard to say - probably you just have to write a complex DAX expression that will repeat the same calculation multiple times; the variables syntax in the next version of DAX (already available in Power BI Designer) will simplify that syntax.

## wilson said:

Has anything changed since writing this article?

I have created the CountRows measure where color is green with and without FILTER & results are identical.

I expected that when using Filter, Any non green rows would have a blank value for the measure but that is not the case.

## Marco Russo (SQLBI) said:

Can you include the two measures you defined?

## Dean said:

Marco, another way to describe what I need is how can I get a summary DAX formula to ignore a specific row context and behave as if another specific row context applied, leaving all other row contexts in place? This would allow me to compare any result to one specific variable as dictated by the row context. Make sense?

## Marco Russo (SQLBI) said:

You have to use CALCULATE/CALCULATETABLE to apply a different filter - you cannot directly change a row context unless you apply an iterator over a filter (not a good idea anyway)

## Dean said:

I'll look into it, Marco. Thanks.

## Saugat said:

Hi Marco,

I have been following your blogs and also attended a SQL conference session and I must say, they are extremely informative.

I have a problem.

I have measures in DAX which perform really poorly when put inside an if statement. Say for example:

E.g. [FX Risk Weighted DKK (BC + QC)] is a DAX measure and when I retrieve it using MDX, it is extremely fast .

This is the MDX to retrieve the measure (simple & straightforward)

select {

[Measures].[FX Risk Weighted DKK (BC + QC, Long)]

}on 0,

Non Empty {[Accounts].[Counterpart ID].children

*[Base Currencies].[Currency Code].children

*[Products].[Product Name].children

} on 1

from [model]

where [Dates].[Date].&[2015-06-30T00:00:00]

However, as soon as I change the DAX measure to: IF([FX Risk Weighted DKK (BC + QC)]>0,[FX Risk Weighted DKK (BC + QC)]), the same MDX to retrieve the measure is extremely slow.

I have SP2 (Since there was this article

https://support.microsoft.com/en-us/kb/2733091)

## Aditya said:

Hi Marco,

This may not be related to CALCULATE(), but I had a problem using the pivot table in excel.

Scenario - I have 2 tables (2 facts essentially) containing their respective measures. When I use a dimensional attribute and 1 fact table and drag it onto the Pivot table everything works fine. Now if I filter on the dimensional attribute for a particular value, say I now get 10 records. This works fine and the pivot table scopes correctly for those 10 filtered records. However, now if I drag a measure from the other fact table, instead of getting values for only these rows, I get a large set of values which seems to be a cross join.

Could you help me identify the problem here as to why this happens and how I could remove this error? I know I haven't added any code or examples to this scenario but I think you get what I mean. If not, I will post an example. Setting me in the right direction would be a great help and thank you.

## Kevin said:

Is it possible to have multiple filters on the same field?

For example, I want to count the number of rows where a Drug Code is in a list of codes.

In regular SQL it would be like this:

SELECT d.DrugName

FROM dim_Drug d

WHERE d.DrugCode IN (90562, 96011, 96001)

How do I simulate the IN clause or filter/count on a list?

## Kevin said:

And really I meant to post was:

SELECT Count(d.DrugName) As Drug_Counts

FROM dim_Drug d

WHERE d.DrugCode IN (90652, 96011, 96001)

## Kevin said:

I've used this, and it correctly returns a count of 2 as I have 2 records in my fact table that have 96039 as a Drug Code.

Psychotropic_DrugCount:=CALCULATE(COUNT(fact_PharmacyClaim[sys_dim_NDC]), fact_PharmacyClaim[sys_dim_NDC]=96039)

But I also want to count rows if they have two other Drug Codes, but when I use the 2nd Measure shown below it acts as an AND filter and since none of the rows have all 3 Drug Codes, the count comes back 'blank' ...so I'm looking for a way to count the number of rows in my fact table, where the Drug is either 96039, 96011, or 96001.

Psychotropic_DrugCount:=CALCULATE(COUNT(fact_PharmacyClaim[sys_dim_NDC]), fact_PharmacyClaim[sys_dim_NDC]=96039, fact_PharmacyClaim[sys_dim_NDC] = 96011, fact_PharmacyClaim[sys_dim_NDC] = 96001)

But the above doesn't count any row with either of those codes, instead looks for a row that satisfies all three criteria and there are none.

Thanks.

## Kevin said:

Ok, figured it out.

Psychotropic_DrugCount:=CALCULATE(COUNT(fact_PharmacyClaim[sys_dim_NDC]), fact_PharmacyClaim[sys_dim_NDC]=96039 || fact_PharmacyClaim[sys_dim_NDC] = 130475 || fact_PharmacyClaim[sys_dim_NDC] = 130334)

## Liz said:

Hi Marco,

I have an interesting dilemma. When I initially built my ssas tabular model, I had a field that contained FIRSTNAME LASTNAME (123) 456-7890. The logic indicates to parse the telephone number when the column encounters a '('. I was able to parse the telephone number from the field by adding two DAX calculations:

CalculatedColumnl: =mid([Assignee Full Name], 1,14)

CalculatedColumn2: =search("(",[Assignee Full Name],1,0)

CalculatedColumn3: =IF([CalculatedColumn3]=0,[CalculatedColumn1],mid([AssigneeFull Name], 1, search("(",[Assignee Full Name],1,0)-1))

However, now because we have international support personnel, the parenthesis have been removed and country codes introduced without parenthesis and even US numbers entered without the parenthesis. My question is how do I edit the logic so it will remove the telephone number regardless of whether a parenthesis exists in the field or not.

I have been working on this for too long and I need help.

## Liz said:

Sorry it should have said

Calculated Column3: =IF([CalculatedColumn2]=0,[CalculatedColumn1],mid([AssigneeFull Name], 1, search("(",[Assignee Full Name],1,0)-1))

And thanks, in advance, for your assistance.

## Marco Russo (SQLBI) said:

@Saugat: the problem is a combination of factor, including use of MDX to query a Tabular model. This is probably fixed in Excel 2016 / Power BI / SSAS 2016. Take a look at this article, considering that SWITCH is like a list of nested IF statements: http://sqlblog.com/blogs/marco_russo/archive/2014/08/18/possible-switch-optimization-in-dax-powerpivot-dax-tabular.aspx

@Aditya: you get more results because the calculation engine doesn't know how to filter what you put in the rows with the measure expression you have in columns. You might write some DAX control to check whether there are other values in other tables for the current selection, but you will not be able (in DAX) to adapt the calculation to any selection made by the user.

@Kevin: DAX does not have a IN syntax, yet. You have to use a list of logical condition in OR, e.g table[col] = 1 || table[col] = 2 || table[col] = 3

@Liz: can you use Power Query to prepare data in advance? You have more functions to manipulate data there. I'm not sure about how you can solve the problem in DAX in a reliable way, considering you don't have regular expressions in DAX. If you know that the phone number is at the end, try to look for the last blank in the string and get everything you have from there to the end if you don't have parenthesis, but there are too many way this could fail...

## Jso said:

Hi Marco,

Thanks for all useful and informative content. How would I go about adding MIN to this function?

=CALCULATE(sum(Table6[Original Est])*110, Values(Table6[State]), Table6[State]="Done", MIN(Table6[Date])

Sum the completed work*110 where state = Done, but only return the records for the earliest DATE record. I'm trying to find the earliest set of records for each UID in my table and sum the value in the original estimate column.

Thank you!!

## Nazmul said:

Hi Marco,

How do you add multiple value as filter in Calculate function. For example, how can I define the query for both Red and Green car?

## ross said:

Is it possible to add 2 different measures in different tables? thanks.

## MSpagnolo said:

I am looking to calculate comp sales Day over Day. I have an store open date and sales date. I believe the formula should be IF Sales Date -365 <= Open Date be Sales else 0 I believe I can do this with calculate how do you do logical operations with calculate.

## Marco Russo (SQLBI) said:

@Jso: use this

=CALCULATE(sum(Table6[Original Est])*110, Values(Table6[State]), Table6[State]="Done", FILTER ( ALL ( Table6[Date] ), Table6[Date] = MIN(Table6[Date]) )

@Nazmul: Use an "or" condition:

= CALCULATE(

COUNTROWS('Demo'),

FILTER( ALL('Demo'[Color]), 'Demo'[Color] = "Green" || 'Demo'[Color] = "Red") )

@ross: yes, just sum them:

= SUM ( table1[col1] ) + SUM ( table2[col2] )

@MSpagnolo:

you should protect the different with an IF, but what do you want to do when you aggregate multiple stores having different open dates? You probably want to create a "same store sales" measure - this is a good idea for a future article (hint: you have to iterate every store with a SUMX, because every store might have a different open date)

## goran said:

Hi Marco,

I have a survey with one question and 3 possible answers. I designed question/answer as single table. After I link table “question/answer” (answer key) with table “FactSurveyResult” (answer key) and bring it in pivot table I got question and only one answer ( answer that patient picked ).

Survey Question Answer AnswerCount

Pain Survey How do you fill not so good 1

My question is there a way to write DAX to show all 3 answers like this:

Survey Question Answer AnswerCount

Pain Survey How do you fill good 0

Pain Survey How do you fill not so good 1

Pain Survey How do you fill bad 0

They want always to see possible answers together with answer patient provided.

Thank you, very much!

## Marco Russo (SQLBI) said:

Goran, did you try the Survey pattern?

http://www.daxpatterns.com/survey/

## goran said:

Thank you Marco, for you respond.

As a matter of fact I did, but I don't think you are covering my situation in your survey pattern. I simple need to show all answers that belong to question in a case of multiple answers, with count 1 for patient answer and 0 for rest of possible answers.

I would greatly appreciated any help here.

Thanks!

## goran said:

I designed the Survey according to your recommendation ( questionanswer (filter) loaded twice with not active relationship )....

## Marco Russo (SQLBI) said:

Goran, I think you just have to convert the blank into a 0.

Did you try with

[YourMeasure] := [Original measure] + 0

It should do the job.

## goran said:

Marco, it returns cross-join like result, and I need result like this:

Survey Question Answer AnswerCount

Pain Survey How do you fill good 0

Pain Survey How do you fill not so good 1

Pain Survey How do you fill bad 0

1 - represents answer patient gave

0 - represents other possible answers for given question.

In multidimensional model I had to load fact table twice and use mdx like:

IIF(

IsEmpty (Measures.[Fact Survey Count]),

IIF(IsEmpty([Measures].[Factless Survey Count]),null,0),

Measures.[Fact Survey Count]

)

I had to create 2 fact tables. One for patient answer and another one for all possible answers. I hope dax has it's way and I don't have to load 2 fact tables since memory storage might be an issue.

Thanks!

## Marco Russo (SQLBI) said:

You can use NATURALINNERJOIN in Excel 2016 or Power BI Desktop, but not in previous versions. However, I don't understand why it doesn't work with the technique discussed before. If you're using a pivot table and you intersect two tables (Survey and Question), you should already see a Crossjoin result until you don't specify a measure in the pivot table - at that point, the measure simply hide those non-existing combinations when it returns blank, but returning 0 you should see them.

## Colm said:

Hi Marco

Very good stuff!

I have created a budget vs actual report using Power Pivot, I have two tables one for budget, separated by month, department and account. The actual table is based off the transaction table,separated by month, department and account. I have successfully been able to create a relationship between these two accounts through adding in other sub tables. The issue is i need to be able to show the variance between budget and sales. This needs to show by month, department and account.

For example:

Sale Depart East Actual Budget Variance

Wages 100 80 (20)

Supplies 100 90 (10)

Sales Depart West Actual Budget variance

Wages 100 80 (20)

Supplies 100 90 (10)

Any help would be greatly appreciated.

Thanks,

## Marco Russo (SQLBI) said:

If you created relationship directly between budget and transactions, it's wrong. If you have same granularity, once you have the measures:

[Actual] := SUM ( Transaction[Amount] )

[Budget] := SUM ( Budget[Budget] )

then you write

[Variance] := [Actual] - [Budget]

If you have to allocate budget on dimensions that you have in Tranaaction and not in Budget, then take a look at http://www.daxpatterns.com/budget-patterns/

## Keith said:

Hi Marco,

I am a newbie to PP and have been scratching my head with this all day long. I am trying to figure out how to take advantage of the automatically calculated "Grand Totals" column to flag my data as ok or nok.

I have various slicers / filters that I can apply to my data to change what the number of rows displayed. I would like to be able to check the value of column "B" against the GT column value and flag as ok if equal and nok if not.

I have been able to do this in a convoluted way using calculated columns, but was wondering if there is a more elegant solution.

Ex data:

Row Labels 0 1 2 Grand Total

Site 1 4 4

Site 2 1 1

Site 3 3 1 4

Site 4 5 7 12

Site 5 4 4

In this example, Site 1 and 5 would be "ok" and the rest "nok".

Hopefully that makes sense.

Thanks,

Keith

## Keith said:

Sorry, I messed up the formating of the data. Hopefully this shows up better:

Row 0 1 2 Grand Total

Site 1 4 0 0 4

Site 2 0 1 0 1

Site 3 3 1 0 4

Site 4 5 7 0 12

Site 5 4 0 0 4

## Marco Russo (SQLBI) said:

Assuming you have a column named "rowid" in the data model and you have a measure named "x" for the total displayed, it would be:

IF (

ISFILTERED ( table[rowid] ),

"" & [x],

IF ( CALCULATE ( [x], table[rowid] = 0 )

= CALCULATE ( [x], ALL ( table[rowid] ) ),

"ok",

"nok"

)

)

You need a rowid because you cannot base any logic on physical order of rows in data model.

## Keith said:

Thank you for our prompt reply Marco. That's much more elegant than the kludge that I put together!