CODEWITHSUNDEEP
ssasmdxcube
Zineb Adam
Zineb Adam

Reputation: 1

SSAS - 'group by' equivalent MDX Calculation

My Fact table looks like this:


ticketID    price           statusID

1              100               1

2              100               1

2              100               2

3              150               1

I am using SSAS to create an OLAP Cube for my data warehouse. I cannot use the aggregateFunction 'Sum' for the measure 'total price', because I will get 450$ instead of 350$ (which is the correct tatal)

Regards

Upvotes: 0

Views: 241

Answers (1)

Tom Huang
Tom Huang

Reputation: 192

Then you can still add a view in db like this:

select 
    ticketid,
    price,
    statusid,
    case when rn=1 then 1 else 0 end as IsMaxStatus 
from
(select ticketid,price,statusid, 
     row_number()over
     (partition by ticketid, price order by statusid desc) as rn 
     from yourFactTb 
) as fact

Then add a dimension [IsMaxStatus] which inlcuded two records of 0/1 in your cube and set the Dim-usage as regular with that measure-group depended on above fact table, and then add a calculated measure say [cal-price] with below formula:

with member [cal-price] as
([Price],[IsMaxStatus].[IsMaxStatus].&[1])

select [cal-price] on 0
from [YourCube]

You can also calculate other measure by this measure-group without the filter of dim [IsMaxStatus]

Hope it helps.

www.mdx-helper.com

Upvotes: 1

Related Questions