Get in DAX what I have in MDX

問題

• 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?

2012年4月25日 上午 08:58

解答

• 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.

• 已標示為解答 2012年4月25日 下午 02:33
• 已取消標示為解答 2012年4月25日 下午 02:33
• 已標示為解答 2012年4月25日 下午 02:35
2012年4月25日 下午 01: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.

• 已標示為解答 2012年4月25日 下午 02:33
• 已取消標示為解答 2012年4月25日 下午 02:33
• 已標示為解答 2012年4月25日 下午 02:35
2012年4月25日 下午 01:30
• Can you give an example? Please

2012年4月25日 下午 02:35
• 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)

2012年4月25日 下午 03:44
• 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

2012年4月25日 下午 07:13
• 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.

2012年4月25日 下午 10:01
• 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

2012年4月26日 上午 11:31
• Hi Lee

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

Thanks

2012年4月26日 下午 08:00
• Lee

Thanks ince again.

I got it all perfect.

2012年4月28日 上午 09:28
• That's great Urielil.
2012年4月29日 上午 07:47