How to use Or filters for getting data from OLAP cube pivot table in Excel

1
votes

We are loading data from an SSAS model (cube) into an Excel pivot table. The data within the spreadsheet needs to be shown using 2 conditions in OR way, I mean, I have 2 fields coming from the database: Original amount and amendment amount, We need display the information coming from the SSAS cube when original amount <=10.000 OR amendment_amount<=10.000.

I tried to use the normal filters on Excel pivot tables, but it doesn't worked, because filters use AND implicitly to display information, I need OR.

I tried to use the normal filters on Excel pivot tables (value and label filters), but it doesn't worked, because filters use AND implicitly to display information, I need OR.

example:
Code    original amount     amendment amount
C1          25000   500
C2          2000    18000
C3          14000   9000
C4          12000   10500

Filtering by Original_amount<=10.000 and amendment_amount<=10.000 is noy displaying any records

The result expected is C1,C2 and C3 code records displayed because one og the both conditions is true... How could I filter for displaying the right data?

Using the example described above, the result expected is C1,C2 and C3 code records displayed because one of both conditions is true...

results expected:
Code      original amount    amendment amount
C1        25.000             500
C2         2.000             18.000
C3        14.000             9.000

How could I filter for displaying the right data?

1
excelssas

1 Answers

0
votes

Create a calculated measure in the PivotTable which is called Show Row:

IIF(
 (
  Not(IsEmpty([Measures].[original amount]))
  AND [Measures].[original amount] <=10000
 )
 OR 
 (
  Not(IsEmpty([Measures].[amendment amount]))
  AND [Measures].[amendment amount]<=10000
 ),
 1,
 Null
)

Then add that calculated measure to your PivotTable and filter to keep only rows where Show Row >= 1.