Filter Not Working Inside Calculate

[azbasketcat]

I'm trying to create a pivot table that displays the most recently used bank for each store. I have a scenario where stores have been changing banks over the last several months and I want to quickly find out what bank a store is currently using.

I am using a single PowerPivot table that contains bank transactions from all stores and all banks with the following columns:

1) Transaction ID (Primary key - unique and sequential)
2) Store ID
3) Date (a store may have 0, 1 or more transactions per day)
4) Amount
5) Bank name

The pivot table will be pretty simple with the Store ID in the rows and the Bank Name in the values.

I created a measure to get the Max Transaction ID = max(Bank[Transaction ID]). This measure works.

However, I can not get my calculate function to filter to one row for each store.

Here is my measure for Most Recent Bank = CALCULATE(Values(Bank[Bank Name]),FILTER(Bank,Bank[Transaction ID]=[Max Transaction ID]))

Any help on why this does not filter to the row equal to the Max Transaction ID for each store is appreciated.

Thanks - Randy

 

[Jacob Barnett]

Randy, you are close!

The piece you are missing is an ALL() on the transaction reference to open out the filter context. Try:

=CALCULATE(VALUES(Bank[Bank Name]),                         
                     FILTER(ALL(Bank[Transaction ID]), Bank[Transaction ID]=MAX(Bank[Transaction ID])
                               )
                    )

Although you can use a separate measure in the FILTER(), it can go wrong when paired with the ALL() so I find it easier conceptually to do it this way in this instance.

 

[azbasketcat]

Jacob - Thanks so much. It worked. I had tried using All() inside and outside the Filter function, but without success. Thanks again, I really appreciate it.

 

[CharlesBanks]

I'm pretty sure this is somewhat similar to the above issue but I can't seem to get the syntax correct.
I’m working with a pivot table

• Connected to external data source in SQL
• NetSales is the measure I am working for value
• Year & DayPart (lunch, dinner) are across the top as columns
• Revenue centers down the left side as rows
• There are multiple years of data

First attempt I was able to get NetSales as a “% or Parent Row” and as a “% of Parent Column”. “% of Parent Total” does not give me the results I am anticipating.
I’m thinking that the result lies in the PowerPivotàCalculated Fields area but am having a difficult time with the syntax.
If I use =[Sum of NetSales]/CALCULATE([Sum of NetSales]) I get 1 which is what I would expect.
When using GetPivotData the following formula returns the number that I wanting as the denominator
=GETPIVOTDATA("[Measures].[Sum of NetSales]",$A$8,"[cvw_DashDate].[FYr]","[cvw_DashDate].[FYr].&[2012]")

So my thought is that the following should get me close to the result that I am wanting
=[Sum of NetSales]/CALCULATE([Sum of NetSales],cvw_DashDate[FYr])

Advance thanks for any insights.

 

[CharlesBanks]

See above