Struggling to get ALL() function to work as expected

[BenNorris]

Hi,

I have a pretty simple setup as follows:

- One 'Data' table of values by Date and by ScenarioID (3 columns in total)

- One table of dates with Date, Month, Quarter and Year (marked as Date Table)

- One table defining two scenarios (2 columns: ScenarioID and ScenarioName)

- Relationship between Date table and Data table (1 to many on Date columns)

- Relationship between Scenarios table and Data table (1 to many on ScenarioID columns)

In the report I have a slicer to allow user to pick one scenario (this slicer is attached to the Scenarios table). I have another two slicers for date selection - one allows them to pick the year and the other a quarter, both attached to the Date table).

What I'd like to be able to do is for some metrics show the sum of the values in the year/quarter selected in the date slicers (this is relatively easy), but for other metrics show the sum of ALL periods up to and including the year/quarter selected. To do this I think I just need to remove the date filters on the data table and apply my own logic, which I thought I could do with the ALL function.

I have approached this in the following way:

Firstly I added a measure in the Date table called 'ReportDate' that returns the MAX of the Date column in that table. Thus, when the user selects and Date/Year in the slicers, this measure returns that value.

Next, in the Data table I've created a second measure where I'm trying to use the ALL() function to remove the date filters from the slicers and apply my own filter using locic. Note that I want to keep the scenario slicer applied. I've tried the following DAX formula with the results as noted:

1. SumAllToReportDate = calculate(sum(tblData[Value]), all(tblDate), filter(tblData, tblData[Date] <= [ReportDate]))
   1. This sums up all the Value items that fall in the quarter of the ReportDate, i.e. the filters from the date slicer have not been removed.
2. SumAllToReportDate = calculate(sum(tblData[Value]), all(tblData), filter(tblData, tblData[Date] <= [ReportDate]))
   1. Produces same result as above
3. SumAllToReportDate = calculate(sum(tblData[Value]), filter(all(tblData), tblData[Date] <= [ReportDate]))
   1. This removes all filters (dates and scenario) from the Data table and sums up to the report date. This is not what I want as I want to keep the Scenario filter applied.
4. SumAllToReportDate = calculate(sum(tblData[Value]), filter(all(tblData[Date]), tblData[Date] <= [ReportDate]))
   1. This produces the same result as 1 & 2 above

I realise I could take item 3 above and re-apply the scenario slicer inside the measure, but in the real analysis that this is being used for there are many slicers and I may need to add more in the future so maintainance will become an issue. Also, it seems like it defeats the purpose of using PowerBI in the first place!

I have also tried only using a single date slicer to drive the date, but I get the same behaviour. I have also tried unmarking the date table as a Date Table but this doesn't help either.

In frustration I removed the relationship between the Date table and the Data table and this meant that 1, 2 and 4 above worked as expected. However, I don't think I want to remove this relationship as otherwise I'll need to create custom measures for every single metric that relies on dates that I want to show in the report (which is pretty much all of them, and there are hundreds), which would again create a big maintainance issue.

Can anyone help on why my ALL function doesn't work? Or is my understanding of how it should work incorrect?

Thanks in advance for any insight!

Ben.

EDIT: Link to pbix file: http://gofile.me/1WJUU/rl8ID3Ani 

The number in the tile for SumAllToReportDate should be 791 if the DAX is working correctly.

[parry2k]

@BenNorris you have provided lot of details but sometime it is too tough to go thru all this. I would recommend, paste sample data and expected output and genius here in community will able to help much quicker. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[BenNorris]

@parry2k - thanks. couldn't figure out how to post pbix file but have tried sharing it now. Could you check you can download from link?