Get in DAX what I have in MDX

# Get in DAX what I have in MDX

• יום רביעי 25 אפריל 2012 08:58

I am trying to get in DAX what I have in MDX and I can't figure how to get the sum of a single member in a measure, no matter what I select in that dimension.

It is needed in many calculated measures, here are two examples I need it for:

1.        Looking at the Budget of a full year while I filter the Actual data on specific date. In MDX it will look like this ([Measures].[Budget] , [Due Date].[All Due Dates].[2012]).
2.        In a  calculated measure that will generate "% of income", from financial accounting data, I want what is grouped in a general Ledger Dimension as income, as a denominator (under an actual measure like this  (  [Measures].[Actual]  /  [Measures].[Actual], [Grouping].[All GLD].[Sales] ) .

How do I get them in DAX?

### כל התגובות

• יום רביעי 25 אפריל 2012 13:30

To remove a filter from the Pivot table you need to use the ALL function or the ALLEXCEPT in conjunction with CALCULATE / FILTER.

DAX is a very different language to MDX hence a mind shift is required.

If you google these functions you'll find the solution is relatively straightforward.

• סומן כתשובה על-ידי יום רביעי 25 אפריל 2012 14:33
• סימון כתשובה בוטל על-ידי יום רביעי 25 אפריל 2012 14:33
• סומן כתשובה על-ידי יום רביעי 25 אפריל 2012 14:35
•
• יום רביעי 25 אפריל 2012 14:35

Can you give an example? Please

• יום רביעי 25 אפריל 2012 15:44

I can't get it only with ALL, ALLEXCEPT ,CALCULATE or  FILTER

Something must be missing. I need to get the amount in the measure  of the chosen member when I select a different member.

What you suggested leaves them in blank.

(I marked it as answered by mistake)

• יום רביעי 25 אפריל 2012 19:13

Urielil,

Can you write something like

=CALCULATE(SUM(BudgetTable[Budget]), 'Due Date'[FiscalYear]=2012)

-- This posting is provided "AS IS" with no warranties, and confers no rights

• יום רביעי 25 אפריל 2012 22:01

Chu, that is not what I am looking for.

What you suggest will give the sum of 2012 only if in a pivot table filter or a slicer I select 2012, but if I will  in the filter select 2011, I will get null. And if I select April 2012, I will get the result for April 2012.

What the MDX I showed dose is show the sum of 2012, no meter what is filtered or sliced, and that is what I am looking for.

• יום חמישי 26 אפריל 2012 11:31

I have created an example book with a DATE table and a FACT table that lists sales by day for products.

I created a relationship between the DATE table and the FACT table.

Then I created the Pivot table with Year and Month Name as slicers and Product on the rows.  Finally, I created a measure in the pivot table below.  This calculates the sales for whatever year is selected on the slicer and ignores whatever is selecte in the month_name slicer.

`=calculate(sum(FACT[Sales]),All(DateTable[MonthName]))`

Also, follow by blog at leehbi.com where I share other DAX tips.

Lee

• יום חמישי 26 אפריל 2012 20:00

Hi Lee

Yes, I think with the example you gave me I found my way.

Thanks

• שבת 28 אפריל 2012 09:28

Lee

Thanks ince again.

I got it all perfect.

• יום ראשון 29 אפריל 2012 07:47

That's great Urielil.