Get in DAX what I have in MDX

# Get in DAX what I have in MDX

• Wednesday, April 25, 2012 8:58 AM

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?

### All Replies

• Wednesday, April 25, 2012 1:30 PM

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.

• Marked As Answer by Wednesday, April 25, 2012 2:33 PM
• Unmarked As Answer by Wednesday, April 25, 2012 2:33 PM
• Marked As Answer by Wednesday, April 25, 2012 2:35 PM
•
• Wednesday, April 25, 2012 2:35 PM

Can you give an example? Please

• Wednesday, April 25, 2012 3:44 PM

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)

• Wednesday, April 25, 2012 7:13 PM

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

• Wednesday, April 25, 2012 10:01 PM

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.

• Thursday, April 26, 2012 11:31 AM

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

• Thursday, April 26, 2012 8:00 PM

Hi Lee

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

Thanks

• Saturday, April 28, 2012 9:28 AM

Lee

Thanks ince again.

I got it all perfect.

• Sunday, April 29, 2012 7:47 AM

That's great Urielil.