Get in DAX what I have in MDX

Question

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?

Answer 1

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.

Answer 2

Can you give an example? Please

Answer 3

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)

Answer 4

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

Answer 5

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.

Answer 6

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]))

You can download a copy of the workbook at : http://db.tt/ah0RAk1T

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

Lee

Answer 7

Hi Lee

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

Thanks

Answer 8

Lee

Thanks ince again.

I got it all perfect.

Answer 9

That's great Urielil.