SSAS - 'group by' equivalent MDX Calculation

0
votes

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

1
ssasmdxcube
Do you want to group by ticketID? – Danylo Korostil
hey! yes this is what I want – Zineb Adam
Is there one price per ticket? – Danylo Korostil
Yes, indeed.... – Zineb Adam
why don't you just filter by statusID 1? – mxix

1 Answers

1
votes

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