Mdx Sum measure of dimension by Date

Question

I'm introducing into mdx language and I came up with a problem. I want to sum up length of streets (DimStreet) from dimension as calculated member in cube with hierarchy date to day level (e.g: [DimDate].[Alldate].[Day]) I done in SQL to give a better understanding:

SELECT f.date_utc_key, SUM(s.length)
    FROM "Fact_coord" as f
    INNER JOIN "DimStreet" as s
    GROUP BY date_utc_key
    ORDER BY date_utc_key asc

Please can you add your attempt in `mdx` ? – whytheq Apr 13 '15 at 12:56 — whytheq, Apr 13 '15 at 12:56

SouravA · Accepted Answer · 2015-04-13T18:23:26.530

0

I am assuming you have a length measure. If so, the below should work.

WITH MEMBER Measures.SumOfLengths AS
SUM
(
   [DimDate].[Alldate].[Day].members, 
   [Measures].[Length]
)


SELECT Measures.SumOfLengths ON 0,
[DimDate].[Alldate].[Day].members ON 1
FROM [YourCube]

edited Apr 13 '15 at 18:23

answered Apr 13 '15 at 14:10

SouravA

5,147
2
24
49

1

hi sourav - why are you including `EXISTING`? If you already have `[DimDate].[Alldate].[Day].members` on rows then you can just chuck `[Measures].[Length]` on columns and only the existing results will be shown on each row - this is SSAS's built-in autoexist behaviour – whytheq Apr 13 '15 at 16:18
hi @Sourav_Agasti - it is possible accomplish the `[Measure].[Length]` if is referenced on dimension and dynamical calculated (like add dynamically on column to fact table)? – Fabio Apr 15 '15 at 10:06

Mdx Sum measure of dimension by Date

1 Answers1