Copying a Measure, but changing the Dimension usage

Question

Hi,

I'm hoping someone can help me with my request, I am new to SSAS and wanted to know if I could do the following?

I have two measures:

• Amout
• Qty

Which run off three dimensions:

• Location
• Transaction Type
• Date

Is there anyway I can copy the Qty measure, but only filter on the location dimension.

Your help would be greatly appreciated.

Andy

---

Robin Parkin

Answer 1

Hi Robin, Can you provide some more details?

What do you mean by copy the Qty measure? and Only filter on the location dimension.

-Thameem

---

Thameem

Answer 2

You could create a calculated member that references the ROOT of all other dimensions, and then it will only display a different value for each level of the Location dimension, but why would you want to do it?

Create Member [measures].[Qty by Location only] AS ([Measures].[Qty], [Dim1].[Attribute1].[All], .... [DimX].[AttributeY].[All])

Answer 3

I have a data structure like so ……

Key                Location    Item             Transaction Date         Transaction Type         Quantity    Amount

1                     LOC1            ABC               01/01/2012           Purchase                           10            $200

2                     LOC1            ABC               01/02/2012           Sale                                  -2            $100

3                     LOC1            ABC               01/03/2012           Sale                                  -2            $100

With this data structure I want to create a measure called Quantity, when I filter on transaction type of Sale this will show me the ‘Sales Quantity’.

I also want to create another version of this measure quantity to not use the transaction type filter, this will be called ‘Inventory Level’

I hope this makes sense!

---

Robin Parkin

Answer 4

CREATE MEMBER [Measures].[Inventory Qty Level] AS ([Measures].[Quantity], [Transaction Type].[Transaction Type].[All])

This should give you a start to what you want. If you need it to ignore another attribute, simply reference it in the measure too, as I have highlighted in bold above.

You can create this in the calculation tab of the cube in BIDS/SSDT or if you simply want it in a query use the WITH MEMBER syntax rather than CREATE above

Answer 5

For your filtered search 'Transaction Type' - Sales, something like this would help.

WITH MEMBER [Measures].[Calculated Quantity] AS [Measures].[Order Quantity] * -1
Select {[Measures].[Calculated Quantity]} ON COLUMNS,
{[Dim1].[Location].[Location],
[Dim1].[Item].[Item]} ON ROWS
FROM [Adventure Works]
Where [Dim1].[Transaction Type].&[Sale]

For full 'Inventory Level' something like this would help.

Select {[Measures].[Quantity]} ON COLUMNS,
{[Dim1].[Location].[Location],
[Dim1].[Item].[Item]} ON ROWS
FROM [Adventure Works]

---

Thameem

Answer 6

Thank you very much for this!  This seems to have done exactly what I wanted to do.  I will give this a through test and let you know the outcome.

---

Robin Parkin