I have a calendar table from =CALENDAR command.I have another table with single action dates. (1 up to 5 dates per year)I have a third table with values to sum. When I select a date from the calendar, I need to get all dates before the selected date, but only after the maximum action day.At the example below, I have 5 action days. I need to get all dates after the third action date, but before the selected date. My formula is not working properly. pseudo-code below.Measure = CALCULATE(SUM(ValueFromThirdTable);FILTER(CalendarTable;ActionTable[date]<=MaxSelectedDateFromCalendar;FILTER(CalendarTable;ActionTable[date]>=MaxActionDateBelowSelectedDateFromCalendar)) Where: MaxSelectedDateFromCalendar = CALCULATE(LASTDATE(calendar[Dates]);ALLSELECTED(calendar[Dates])) -> this is working great when I check on KPI card. and:MaxActionDateBelowSelectedDateFromCalendar = CALCULATE(MAX(ActionTable[Date]);FILTER(ActionTable;ActionTable[Date]<=MaxSelectedDateFromCalendar )) -> this is working great when I check on KPI card. Is it the double filter my problem?It seams it does not apply the second filter rule...

my relationship between table goes as follow:CalendarTable connects date field to ThirdTable with values, dates and client name.ThirdTable connects client name to ActionTable where I get the action date on where a person was in charge for that client.Example: I select the date 01/apr/2017 from CalendarTable. This date returns values from some clients in the ThirdTable. Then, I need to calculate from ActionTable who was in charge for that client at that date, and I know John Doe was in charge of that client since 01/jan/2017 because this is the max date before the selected date. This is why there are just a few dates on my ActionTable. If this client was being taken care by Jane Doe since 01/dec/2016, I dont want the measure to ever see Jane Doe, only the most recent action date from John Doe must be seen, and then my final result shows only the values from the selected date pointing to John Doe as the person in charge on that client. I changed the table name as instrucetd in steps 2 and 3, and now I get all dates, before and after the selected date. I got even worse than before.

Anonymous Hi, Could you please post a little sample? I create a dummy one. But there must be something wrong. It's hard to create relationship between ThirdTable and ActionTable. If you can make sure your two measures about date are right, maybe you can try this. Or you can post your real formula here.Measure = CALCULATE( SUM(ValueFromThirdTable); FILTER(all(CalendarTable); CalendarTable[date]<=MaxSelectedDateFromCalendar&&CalendarTable[date]>=MaxActionDateBelowSelectedDateFromCalendar)) Best Regards!Dale

nice dummy load, it is correct and similar to my tables. I updated your example. If I select a date from CalendarTable, this date will filter all clients with the same date in the ThirdTable.Then, my measure needs to filter through the ActionTable and find only the last date before the selected date.According to the example, my final result in the table object must show only the following information:clientvaluepersonA100JackB200MarkC500Linda But so far, the value is repeating to all people, which is incorrect:clientvaluepersonA100John DoeA100Jane DoeA100JackB200MarkC500AmyC500Linda Why do you say it is difficult to create relationship between tables? Is it the many to many relationship? I created a linktable with distinct values and then I can connect them.I tested your ALL suggestion, and as expected it just ignores the selected date and calculates to all values from all dates in the calendarTable.

Anonymous Hi, It's a many to many relationship. But it won't be a problem due to there is a link table. The reason that you didn't get the right answer is you invoked a measure which would conduct a context transition when invoked. There just is a row context iterated by filter. You should change your formula like this.MaxSelectedDateFromCalendar = CALCULATE ( LASTDATE ( CalendarTable[Date] ), ALLSELECTED ( calendartable[Date] ) )MaxActionDateBelowSelectedDateFromCalendar = VAR MaxInCalendar = [MaxSelectedDateFromCalendar] RETURN CALCULATE ( MAX ( ActionTable[Date] ), FILTER ( ActionTable, ActionTable[Date] <= MaxInCalendar ) )Measure = VAR MaxInAction = [MaxActionDateBelowSelectedDateFromCalendar] VAR MaxInCalendar = [MaxSelectedDateFromCalendar] RETURN CALCULATE ( SUM ( ThirdTable[Amount] ), FILTER ( ALL ( CalendarTable ), AND ( CalendarTable[Date] >= MaxInAction, CalendarTable[Date] <= MaxInCalendar ) ) )There is a demo in the picture. I have added some data to make it more like yours. Best Regards!Dale

Anonymous

Not applicable

9 years ago

double filter from 2 date tables

I have a calendar table from =CALENDAR command.

I have another table with single action dates. (1 up to 5 dates per year)

I have a third table with values to sum.

When I select a date from the calendar, I need to get all dates before the selected date, but only after the maximum action day.

At the example below, I have 5 action days. I need to get all dates after the third action date, but before the selected date.

My formula is not working properly. pseudo-code below.

Measure = CALCULATE(
SUM(ValueFromThirdTable);
FILTER(CalendarTable;ActionTable[date]<=MaxSelectedDateFromCalendar;
FILTER(CalendarTable;ActionTable[date]>=MaxActionDateBelowSelectedDateFromCalendar))

Where: MaxSelectedDateFromCalendar = CALCULATE(
LASTDATE(calendar[Dates]);
ALLSELECTED(calendar[Dates])) -> this is working great when I check on KPI card.

and:

MaxActionDateBelowSelectedDateFromCalendar = CALCULATE(
MAX(ActionTable[Date]);
FILTER(ActionTable;
ActionTable[Date]<=MaxSelectedDateFromCalendar )) -> this is working great when I check on KPI card.

Is it the double filter my problem?

It seams it does not apply the second filter rule...

Need Help

12 Replies

v-jiascu-msft
Microsoft Employee
9 years ago
Anonymous

Hi,

I guess the relationships in you mode look like this: Calendar Table with Third Table. Action Table is a single table. It should be like this. Please check it out.
The blue parts should be changed into “CalendarTable”. Please have a try.
Measure = CALCULATE( SUM(ValueFromThirdTable); FILTER(CalendarTable;ActionTable[date]<=MaxSelectedDateFromCalendar); FILTER(CalendarTable;ActionTable[date]>=MaxActionDateBelowSelectedDateFromCalendar))
3.If you change it like this, its performance would be better.
Measure = CALCULATE( SUM(ValueFromThirdTable); FILTER(CalendarTable;
CalendarTable[date]<=MaxSelectedDateFromCalendar&&CalendarTable[date]>=MaxActionDateBelowSelectedDateFromCalendar))

Best Regards!
Dale
- Anonymous
  Not applicable
  9 years ago
  my relationship between table goes as follow:
  CalendarTable connects date field to ThirdTable with values, dates and client name.
  ThirdTable connects client name to ActionTable where I get the action date on where a person was in charge for that client.
  Example: I select the date 01/apr/2017 from CalendarTable. This date returns values from some clients in the ThirdTable. Then, I need to calculate from ActionTable who was in charge for that client at that date, and I know John Doe was in charge of that client since 01/jan/2017 because this is the max date before the selected date. This is why there are just a few dates on my ActionTable. If this client was being taken care by Jane Doe since 01/dec/2016, I dont want the measure to ever see Jane Doe, only the most recent action date from John Doe must be seen, and then my final result shows only the values from the selected date pointing to John Doe as the person in charge on that client.
  
  I changed the table name as instrucetd in steps 2 and 3, and now I get all dates, before and after the selected date. I got even worse than before.
  - v-jiascu-msft
    Microsoft Employee
    9 years ago
    Anonymous
    
    Hi,
    
    Could you please post a little sample? I create a dummy one. But there must be something wrong. It's hard to create relationship between ThirdTable and ActionTable. If you can make sure your two measures about date are right, maybe you can try this. Or you can post your real formula here.
    Measure = CALCULATE( SUM(ValueFromThirdTable); FILTER(all(CalendarTable); CalendarTable[date]<=MaxSelectedDateFromCalendar&&CalendarTable[date]>=MaxActionDateBelowSelectedDateFromCalendar))
    
    Best Regards!
    Dale

Forum Discussion

double filter from 2 date tables

12 Replies

Related content

Filtered table

Double Filter

Double filter formula

Can I filter the 1st fact table which then filters a 2nd fact table?

Count function is doubling