Forum Discussion
double filter from 2 date tables
Anonymous
Hi,
2. This formula can bring us the right person in the selected period.
CurrentClient =
VAR ValidRecentDate =
CALCULATE (
MAX ( ActionTable[Date] ),
FILTER ( ActionTable, ActionTable[Date] <= MAX ( CalendarTable[Date] ) )
)
RETURN
CALCULATE (
MIN ( ActionTable[Sales] ),
FILTER ( ActionTable, ActionTable[Date] = ValidRecentDate )
)3. We can see that you want the sum of a period from the formula in your first post. If you just wanted the total of the selected day and person. you could use this:
Measure 2 = SUM ( ThirdTable[Amount] )
PLease have a try.
Best Regards!
Dale
I started with a new approach.
I still have my calendar table, action table, and value table (we were refering as third-table).
But now I am trying to join tables and merge them together, this way I may get the action on each row when it happens.
starting tables:
Action table (8rows)
| date | action | client |
| 01/01/2017 | a | aa |
| 01/06/2016 | b | aa |
| 01/01/2006 | c | aa |
| 01/01/2006 | d | aa |
| 01/01/2017 | e | bb |
| 01/06/2016 | f | bb |
| 01/06/2016 | g | bb |
| 01/01/2016 | h | bb |
Values table
| date | client | values |
| 1/4/16 | aa | 1 |
| 1/8/16 | aa | 2 |
| 1/8/16 | aa | 4 |
| 1/1/17 | aa | 8 |
| 1/2/16 | bb | 16 |
| 1/6/16 | bb | 32 |
| 1/2/17 | bb | 64 |
what I want after join: (10rows, repeating actions for some dates)
| date | client | values | date(action) | action(action) |
| 01/04/2016 | aa | 1 | 01/01/2016 | c |
| 01/04/2016 | aa | 1 | 01/01/2016 | d |
| 01/08/2016 | aa | 2 | 01/06/2006 | b |
| 01/08/2016 | aa | 4 | 01/06/2006 | b |
| 01/01/2017 | aa | 8 | 01/01/2017 | a |
| 01/02/2016 | bb | 16 | 01/01/2016 | h |
| 01/06/2016 | bb | 32 | 01/06/2016 | f |
| 01/06/2016 | bb | 32 | 01/06/2016 | g |
| 01/02/2017 | bb | 64 | 01/01/2017 | e |
So far, in the query editor, I managed to get all action dates before the values date.
But now I just need to filter only the maximum action date for each values date.
I know how to do this easily in Excel, but not Power BI...
As another result, I can accept only the first action per row, even when there are 2 actions in the same date.
This way, I will get the same total rows, but 2 extra columns.
| date | client | values | date(action) | action(action) |
| 01/04/2016 | aa | 1 | 01/01/2016 | c |
| 01/08/2016 | aa | 2 | 01/06/2006 | b |
| 01/08/2016 | aa | 4 | 01/06/2006 | b |
| 01/01/2017 | aa | 8 | 01/01/2017 | a |
| 01/02/2016 | bb | 16 | 01/01/2016 | h |
| 01/06/2016 | bb | 32 | 01/06/2016 | f |
| 01/02/2017 | bb | 64 | 01/01/2017 | e |
Any help?
Anonymous
Hi,
Sorry for so late. Are the tables real tables from production. If they were, they may be the root cause because of the duplicate rows. Let's follow your idea.
1. Merge the two tables. There must be many duplicate rows.
2. Add an index to deal with the rows with same date in Action Table.
3. Try this formula as a calculated column.
Column = VAR MaxActionDate = CALCULATE ( MAX ( 'Merge1'[NewColumn.date] ), FILTER ( 'Merge1', 'Merge1'[date] = EARLIER ( 'Merge1'[date] ) && 'Merge1'[client] = EARLIER ( Merge1[client] ) && 'Merge1'[NewColumn.date] <= EARLIER ( Merge1[date] ) ) ) VAR MinIndex = CALCULATE ( MIN ( 'Merge1'[Index] ), FILTER ( 'Merge1', 'Merge1'[NewColumn.date] = MaxActionDate && 'Merge1'[date] = EARLIER ( 'Merge1'[date] ) && 'Merge1'[client] = EARLIER ( Merge1[client] ) ) ) RETURN IF ( 'Merge1'[NewColumn.date] = MaxActionDate && 'Merge1'[Index] = MinIndex, 1, 0 )4. Filter the qualified rows to a new table.
Table = filter('Merge1','Merge1'[Column]=1)Please have try.
Best Regards!
Dale