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!

## John said:

Is Grouping by multiple fields from multiple table possible in DAX?

I am trying to join 2 tables by Currency and Multiply Amount from one table with conversion factor on other.

Table1: TRANSACTION

CURRENCY AMOUNT

USD 100

INR 2000

GBP 80

Table2: CONVERSION

CURRENCY TARGET_CURRENCY CONVERSION_FACTOR

USD USD 1

USD INR 60

USD GBP .6

INR USD .1667

INR INR 1

INR GBP .01

GBP USD 1.6667

GBP GBP 1

GBP INR 100

I am trying to do following through DAX

Select T.CURRENCY, C.TARGET_CURRENCY, SUM(T.AMOUNT*C.CONVERSION_FACTOR)

FROM TRANSACTION T

JOIN CONVERSION C ON T.CURRENCY = C.CURRENCY

GROUP BY T.CURRENCY, C.TARGET_CURRENCY

I would appreciate any help with this.

## Marco Russo (SQLBI) said:

You can try this:

EVALUATE

ADDCOLUMNS (

CROSSJOIN (

VALUES ( Transaction[Currency] ),

CALCULATETABLE (

VALUES ( Conversion[Target_Currency] ),

FILTER (

ALL ( Conversion[Currency] ),

Conversion[Currency] = Transaction[Currency]

)

)

),

"Converted Value", CALCULATE (

SUM ( Transaction[Amount] ) * VALUES ( Conversion[Conversion_Factor] )

)

)

## John said:

Hi Marco,

Thank you for your reply. Though I am trying to use your suggestion I am getting the following error:

Executing the query ...

Query (17, 39) The value for column 'CURRENCY' in table 'TRANSACTIONS' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

Execution complete

Can you please suggest anything. I really appreciate your help.

Thank you.

## John said:

I have created a bridge table Currency_Bridge to handle the Many-to-Many in real life and it has the following values

Currency_Code Currency Name

USD US Dollar

GBP Great Britain Pound

INR Indian Rupees

And in my Transaction table I have added few more rows.

CURRENCY AMOUNT

USD 100

INR 2000

GBP 80

USD 500

GBP 900

INR 5000

GBP 1000

## Marco Russo (SQLBI) said:

Probably I used CROSSJOIN instead of GENERATE - try this:

EVALUATE

ADDCOLUMNS (

GENERATE (

VALUES ( Transaction[Currency] ),

CALCULATETABLE (

VALUES ( Conversion[Target_Currency] ),

FILTER (

ALL ( Conversion[Currency] ),

Conversion[Currency] = Transaction[Currency]

)

)

),

"Converted Value", CALCULATE (

SUM ( Transaction[Amount] ) * VALUES ( Conversion[Conversion_Factor] )

)

)

## David said:

Hi Marco,

I have a problem with the calculate function. Im trying to make a caluculate within the PowerPivot tabel for sales data where each data row contains one unique products with Product ID that the customers has bought sales value quantity etc. Many different products could have been bought on a receipt where each row have a Receipt ID.

Im trying to count nr of unique products that have been bought and create a new row in that PowerPivot Tabel explain on each row how many unique products that have actually been bought all in all within that receipt.

The formula im been trying but don’t get to work is something like this:

=Calculate(distinctcount(Product ID); Filter(Sales Data; Receipt ID=Receipt ID)

Could you help me please. Been at this problem for a month now!

Kind regards,

David

## David said:

Hi,

Just want to add that when i type the formula above its ruturns the total nr of products not nr of unique products fore each recipt.

Kind regards,

David

## Marco Russo (SQLBI) said:

I think you have to write something like:

CALCULATE (

DISTINCTCOUNT ( Table[ProductID] ),

ALLEXCEPT ( Table, Table[ReceiptID] )

)

## David said:

Thanks for the quick reply!

I didnt work =(. Got an error saying "The ALLEXCEPT function expects a table reference expression for argument '2', but a string or numeric expression was used"

Kind regards,

David

## Marco Russo (SQLBI) said:

You have to replace "table" with your table name

## Makarand Lele said:

Hi Marco,

I have been reading up on DAX and also bought your book recently for a project that I am doing at work using the tabular model.

I have a requirement whereby a measure that was already calculated for a set of filter values, needs to be used in a measure for a different set of filter values. This is akin to a cell A1 in an excel spreadsheet which has been calculated using a formula and there is a different cell B6 also based on a formula but which references cell A1 in its own formula. I thought a CALCULATE with Filter would do the job. The Filter would allow me to retrieve a previous result which I could then use for my new Measure calculation. I would have liked to upload a picture, but can't do so in this box. In my old excel spreadsheet I have a cell H18 (Forecast rate) which is a value produced for a set of Dimensions Region=NETHERLANDS, Season=W15, Toolset=FX and Currency=USD. Further down on the same excel spreadsheet I have another value that makes the use of same dimension except the Toolset which is FX Option. Cell H50 makes use of the formula which is IF(E50=0,0,(J50-H18)/H18). I have been able to achieve everything using DAX except this calculation. 'Forecast Improvement' which is the measure I need to produce for the FX Option toolset, needs to make use of H18 which is a measure previously defined as 'Forecast Rate' - same as Cell H18. The current value for H18 is 1.1542.

This measure is calculated in DAX as follows

FCast_Rate:=IF(ISBLANK([DealtRowCount]),[FCast_Counter], [FCast_Dealt]). This value would be for H18 in the excel spreadsheet.

I then define another measure as follows

FCast_RateFX:=CALCULATE([FCast_Rate],'D-Toolset'[toolset]="FX")

I then use the above in the following measure

FCast_Improvment:=([Calc3]-[FCast_RateFX])/[FCast_RateFX]. This value would be for H50 in the excel spreadsheet

But for some strange reason I am getting a value of 1.1278 instead of 1.1542 in the FCast_RateFX. I am not sure how the filters work. I thought because we have put in the filter of 'D-Toolset'[toolset]="FX", this would replace 'D-Toolset'[toolset]="FX-Option" of the current context. But it seems to be doing something else instead.

Do you feel my understanding of CALCULATE is correct in sense of how the filters are treated? Thanks in advance Marco.

## Makarand Lele said:

Just to make clear.. I feel the FX-Option filter would be replaced with the FX filter for the current context for the calculation of this measure.. while all other filters remain intact which are being derived from the data model relationships and how I want the report to be displayed i.e. with the Region=NETHERLANDS, Season=W15, Toolset=FX and Currency=USD filter.

Thanks

Makarand

## Sam said:

How can I calculate the average of two Measure values from two different tables? i.e tbl 1-measure=2015 total emp. count, table 2-measure= 2016 total emp. count. need the average emp. count. Please help.

Thanks.

## Igor Zhilin said:

Hi Marco. I have an SQL and QlikView background. Your post helped me understand a bit more the DAX CALCULATE function.

However, I struggle to understand the point with FILTER and "all the columns". My understanding is that FILTER is like a WHERE clause (= returning records / rows), and if we are talking "columns" then it means there is also a SELECT logic involved in the logic of FILTER... I think I may be making it more complicated than it is.

Could you please elaborate on this part? What is the difference on FILTER('Table' ) vs FILTER('Table'[Column])?

## Magnus Nilsson said:

Hi Marco,

Greate article though I'm not experiencing the expected behaviour. You seem to write that any reference to a column in a calculate filter expression overrides the query context on that column? I'm doing this in the latest Power BI Desktop version.

I have a Invoice table with DueDates that are in an active relationship with a Date tables primary key column Day.

If I use the measure below and slice it by the Date dimension per month it still returns the number of invoices per month for each of the month cells/buckets, it does not aggregate the number of invoices month over month which I thought would be the expected behaviour if the FILTER indeed overrides the query context slice filter on the particular column. If so I would get all the Invoices who's DueDate was before or at the last day in the particular cell/bucket, no?

CountSaldo =

CALCULATE(

COUNTROWS(Invoice);

FILTER(ALL(Invoice[DueDate]);

Invoice[DueDate] <= MAX(Date[Day])

)

)

If I instead use ALL(Invoice), ie clear the filter on the whole Invoice table I get the expected behaviour. Now the number of invoices are aggregated over time, but this ruins the possibility to slice the measure by other Invoice related diemsions, like customer or region.

Is it possible to only remove the query context filter on the duedate column but not the other?

## Marco Russo (SQLBI) said:

@Makarand: your assumptions seem correct, I cannot say what could be the iseue without analyzing the entire model and report

@Sam: can you clarify the question?

@Igor: I suggest you to read more recent and deeper articles, such as:

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

https://www.sqlbi.com/articles/understanding-context-transition/

A more complete description is available in this book:

https://www.sqlbi.com/books/the-definitive-guide-to-dax/

@Magnus: you can use the book above :) and read this article:

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

Short explanation: you have to realize the difference between a column filter and a table filter, which applies to an expanded table (=base table + all lookup tables reachable through relationships)

## Rabih Charara said:

I have the world currencies rates loaded between Monday and Friday every week. Then I have a gap of Saturday and Sunday. If we sell any item during the weekend I want to fall back to Friday rate when I make the conversion from any currency to US Dollar.

So the Canadian dollar to US dollar rate was:

4/29/2017 .73136839

5/02/2017 .73217162

5/03 some rate

5/04 some rate

5/05 some rate

5/06/2017 .73030015

5/09/2017 .72955424

So my transactions carried in Canadian dollar on 4/30 and 5/01 must pick the 4/29 rate and the transactions in Canadian dollar on 5/07 and 5/08 must pick up the 5/06 rate. How can I do that in Power BI? Thank you

## Rabih Charara said:

Hello Marco,

Bought your book and followed the example about exchange but it didn't work. I downloaded the accompanying database, but the result is wrong. Below is the output of running the code on page 391. I got it by running the query from DaxStudio.

Currency Sales in USD Currency Sales

US Dollar 3805392024.21361 3805392024.21357

Armenian Dram 3805392024.21361 1269282737849.36

Australian Dollar 3805392024.21361 4654899465.72616

Thai Baht 3805392024.21361 126320094175.723

Canadian Dollar 3805392024.21361 4146590249.55027

Danish Krone 3805392024.21361 20229305593.3903

## Marco Russo (SQLBI) said:

Rabih, I'm not sure about what example you tried, I suggest you to write in the web page of the corresponding book on www.sqlbi.com indicating the exact page and formula.

Regarding your problem, you should be able to move part of the calculation in a calculated column, so that you don't have to look for the right date at query time (which would be more expensive).

I hope it helps,

Marco

## Rabih Charara said:

Thank you for taking the time to reply Marco. I will write the details on sqlbi.com.

## DonV said:

Hi Marco I bought your book the definitive guide, I am new to DAX, and I have been looking and reading explanation about the use of the CALCULATE function.

Is it the case that it is used to filter values during roww context aggregation, and if no filtering is required in the calculation then a SUMX or any X type aggregate function should be used instead of CALCULATE. related

## Marco Russo (SQLBI) said:

No - you have an entire book with a much detailed explanation!

## Chris said:

Hi, Marco. Thank you for all you've done for the Microsoft BI community! Your sites and blogs are so helpful. I have a PowerPivot DAX problem that I have not been able to resolve despite reading many blogs and help pages, and I would be very grateful if you could assist this humble rookie.

I have a PowerPivot table with rows broken down by sales division, product category, etc. The columns are fiscal year and fiscal month. There are a handful of measure being displayed. I need to copy one of those measures, and keep all of the context except that the value should represent the same month in the previous fiscal year. The data source is a single SQL view.

Instead of trying to do something on the data side, I've been trying to create a custom measure using the Calculate function. I've tried different permutations/combinations of the Filter and Value function without success. I either lose the context of the rows, or get the same value as the current year's month, or get an error that it can't determine a single value for the month column. An example of one of the formulas I've been trying is:

=CALCULATE(

SUM(viewname[ForecastDollars]),

FILTER(ALL(viewname[ForecastMonth]),MIN(viewname[ForecastMonth])-100),

VALUES(viewname[ForecastMonth])

)

The ForecastMonth values are numeric, like "201712". In a separate custom measure (formula below), I've been able to correctly get the prior year's month (like "201612"). But I've been unable to get this to work within the above-mentioned formula.

=MIN(viewname[ForecastMonth])-100

Thank you in advance for your help with this context problem, which I'm sure is quite elementary for you. Please let me know if you need any other information.

## DonV said:

Hi Chris and Marco this issue relates to my Post Prior to Chris about the use of CALCULATE, it looks like the SUMX Function could be used instead of CALCULATE. As a newbie to DAX could either of you take the time to explain the use of CALCULATE and/orSUMX

I have bought the book the Definitive Guide to DAX by Marco Ii am just around pg50 however I would like to know how and when to use CALCULATE instead of a a X function for filtering and aggregation

## Chris said:

Don: Sorry, but I'm not qualified to comment on that. ;-)

## Marco Russo (SQLBI) said:

@DonV: just read the book, or get three hours to watch the free Introduction to DAX video course https://www.sqlbi.com/p/introducing-dax-video-course/ (then study deeper in the book or in the Mastering DAX video course!).

The thing is that SUM and SUMX are the same function, so CALCULATE changes the way they work, you cannot replace one with another.

@Chris: it's a bad idea not having a date table. If you don't have any other date/year/month columns, you could use this one:

CALCULATE (

SUM ( viewname[ForecastDollars] ),

FILTER (

ALL( viewname[ForecastMonth] ),

CONTAINS (

VALUES ( viewname[ForecastMonth] ),

viewname[ForecastMonth],

viewname[ForecastMonth] - 100

)

)

)

But it would be much better to get a date table and use time intelligence functions, or http://www.daxpatterns.com/time-patterns/ if you have a special calendar.

## DonV said:

Thanks Marco I will take a look at the videos