2012年4月25日 上午 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:
- 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].).
- 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日 下午 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:35
Can you give an example? Please
2012年4月25日 下午 03: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)
2012年4月25日 下午 07:13
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日 下午 10: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.
2012年4月26日 上午 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.
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.
2012年4月26日 下午 08:00
Yes, I think with the example you gave me I found my way.
2012年4月28日 上午 09:28
Thanks ince again.
I got it all perfect.
2012年4月29日 上午 07:47That's great Urielil.