double filter from 2 date tables

[Anonymous]

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...

[v-jiascu-msft]

@Anonymous

Hi,

1. 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.
2. 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]

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]

@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

[Anonymous]

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:

client	value	person
A	100	Jack
B	200	Mark
C	500	Linda

But so far, the value is repeating to all people, which is incorrect:

client	value	person
A	100	John Doe
A	100	Jane Doe
A	100	Jack
B	200	Mark
C	500	Amy
C	500	Linda

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.

[v-jiascu-msft]

@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]

thanks for your help, but I still get the same worng results.

I tested all your code, using VAR etc.

what is your measure for CurrentClient? I think the real secret is in this measure...

I tried to copy your third measure below, but changing the SUM to return lastnonblank from actiontable[sales].

this way, I get all sales names before the selected date, not only the first name.

[v-jiascu-msft]

@Anonymous

Hi,

Sorry, it's my mistake. We can't get the right client directly because of the relationship. It will display all the clients in the other side of relationship. Here is the formula for CurrentClient. Have a try please.

CurrentClient =
CALCULATE (
    MIN ( ActionTable[Sales] ),
    FILTER ( ActionTable, ActionTable[Date] = MAX ( ActionTable[Date] ) )
)

Best Regards!

Dale

[Anonymous]

I tested this new measure of yours. Still not ok my final result.

When I select a Date from CalendarTable, I need to see only:
1-Name of all clients for that period;

2-Name of only one person in charge for each client;

3-Total amount for that person/client;

I understand your measures, I tried to improve and test extra codes over your measures.

For my final table object, I put only measures on it.

Using all your final measures and suggestions, now I have:

1-only one time the name of the client: correct;

2-only one name of the person in charge, but it is actually the most recent name, even after the selected date: not correct;

3-total amount calculated of ALL values: not correct;

[v-jiascu-msft]

@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

[Anonymous]

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?

[v-jiascu-msft]

@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

[Anonymous]

I will try to use another approach:
I will crate a new calculated column in my ThirdTable, comparing the dates between ThirdTable and ActionTable.
This way, when I filter a date from calendar, it will already read the data form this new calculated column.

Now I just need to figure how to make this DAX for Column, and not for Measure.