ALLEXCEPT problem

[Anonymous]

I have a simple table set up:

I'm creating an array that uses a DateDimension[BiWeekly PPE] slicer.

For a row in the array, I need to override the slicer, as I need all numbers to be displayed after the date in the slicer.

I have tried the following:

Number.M =
Calculate(sum(Table2[Number]),
filter(ALLEXCEPT(Table2,Table2[UniqueID]),
Table2[Date]>SELECTEDVALUE(DateDimension[BiWeeklyPPE])))
It's very close to being correct, but I need the Table1[Name1] data in the array and I can't figure out how to filter properly.

Result I get:

Result I need:

Edit:

If I use Table2[UniqueID] as the column entity, it works. so I guess something to do with using Table1[Name1] as a column header.

Any way to fix it?

Appreciate any ideas; TYIA

[amitchandak]

@Anonymous , better to use after date slicer. You have an option for between, before, and after, and the small down arrow

If you want to display more dates than you have selected, you need an independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])

Calculate(sum(Table2[Number]),
filter(ALLEXCEPT(Table2,Table2[UniqueID]),
Table2[Date]>_max ))

Assuming the rest of the code is correct

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Anonymous]

@amitchandak 

"better to use after date slicer. " use what after the date slicer?

"You have an option for between, before, and after, and the small down arrow" for what?

"If you want to display more dates than you have selected, you need an independent date table" sorry if this wasn't clear - the DateDimension in the picture above is a date table. it has dates spanning 3 years.. I was just giving a sample

//Date1 is independent Date table, Date is joined with Table  yes, that is how DateDimension[Date] is

new measure =
var _max = maxx(allselected(Date1),Date1[Date])

Calculate(sum(Table2[Number]),
filter(ALLEXCEPT(Table2,Table2[UniqueID]),
Table2[Date]>_max ))

This returned the same result:  

Edited to add: it's the "ALLEXCEPT(Table2,Table2[UniqueID]" that doesn't seem to do what I expect it to do in this context.. 

[amitchandak]

@Anonymous , Options

When you select a date, you get data for more than the selected date. But you can display dates more than that. there you need an independent date table. I have shared video link for that

[Anonymous]

@amitchandak oh i see your meaning. I'm using dropdown in the slicer. User selects a BiWeekly PPE.

The issue is I am losing the relationship between Table1 and Table2 with this formula.

Column Headers come from Table1; Number.M needs to come from Table2.

If I set it up so that the Column Headers came from Table2 it would work fine, but it's not possible. How to restore the relationship/link between the tables?

[Anonymous · Accepted solution]

Ok! Table 1 needs added to AllExcept filter:

Number.M =
Calculate(sum(Table2[Number]),
filter(ALLEXCEPT(Table2,Table1,Table2[UniqueID]),
Table2[Date]>SELECTEDVALUE(DateDimension[BiWeeklyPPE])))