daxmdx
I needed a little help on working with MDX and DAX. Recently I have just started working with these two terms and am having a little trouble figuring out the differences and possible examples of how they work.
The articles I have read online are giving me a little trouble and I am finding it hard to understand. It would be great if someone could, maybe, kindly explain it in simple words what they are and what they do?
Thank you so much for the help!
You need first to define a measure that will compte the median price across the siblings:
[Measures].[Siblings_median_price]
AS
MEDIAN([DimItems].[Dim Items Id].CurrentMember.Siblings, [Measures].[Price])
The following measure should return what you want:
[Measures].[Sum_below_median]
AS
SUM(
[DimItems].[Dim Items Id].CurrentMember.Children
, IIF([Measures].[Price] < [Measures].[Siblings_median_price]
, [Measures].[Quantity], Null)
)
You can achieve your desired results by using a crossjoin (the * notation) and putting the dimensions on the columns axis. Here's an example based upon the Adventure Works SSAS DB.
select {} on 0,
{
[Customer].[Gender].[Gender].members
*
[Date].[Calendar Year].[Calendar Year].members
} on 1
from [Adventure Works]
Best Answer
MDX & DAX are query languages for OLAP cubes.
MDX is used on multidimensional OLAP cubes and is used by many vendors.
DAX is Microsofts own query language and can only be used in a tabular OLAP cube or within Excel.
Assuming you are using Microsoft SSAS you must choose which type of cube you want when you install SSAS.
If you (or someone else) has already installed SSAS then you need to find out if its Multidimensional or Tabular, and then use the appropriate query language.