Calculated table , addcolumns and calculate to filter columns

[Applicable88]

Hello,

I think I run into a typical problem, in the approach to filter an original table with a calculated table. The problem is that context transition is not working out, and I followed the double calculate solution but the results doesn't change.

I have following sample table:

ID	Startime	Finished	Email	Name	Weekly Jobs	Daily Jobs
1	25.3.22 10:00	25.3.22 10:00	peter@...	Peter	Change gear	No Action
2	26.3.22 10:00	26.3.22 10:00	john@...	John	Cleaning cloth	No Action
3	27.3.22 10:00	27.3.22 10:00	peter@...	Peter	Change gear	Check Light
4	28.3.22 10:00	28.3.22 10:00	steve@...	Steve	Cleaning cloth	Check Light

I now want a calculated table where I only look into the weekly jobs , and add the email and the finished time. And I also only want to see the most last finished time of the two different weekly job and the email of the person:

Weekly2 = 
CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE (
            Table1,
            Table1[Weekly Jobs]
        ),
        "Last Check",
            CALCULATE (
                MAX ( Table1[Finished] )
            ),
        "E-Mail",
            CALCULATE (
               calculate( Max (Table1[Email] )
            )
    ),
    Table1[Weekly Jobs]
        <> BLANK ()
)

There should be only two rows :

Last Checked	E-mail		Weekly Jobs
27.3.22 10:00	peter@...		Change gear
28.3.22 10:00	steve@...		Cleaning cloth

But instead the context transistion for the email is not working he shows max email in alphabet of the whole original table to the actual right timestamp and weekly job:

Last Checked	E-mail		Weekly Jobs
27.3.22 10:00	peter@...		Change gear
28.3.22 10:00	John@...		Cleaning cloth

That means the context transistion is not working for email. But strangely the context transition works for "Last Checked", but I think its a coincidence and that it also might fail when data changes.

Is there a better way to filter a original table or other ways to reach a correct context transistion? I also tried with double calculate:

calculate (calculate(max ( Table1[Email]) )

But no changes.

Thank you very much in advance.

Best. 

[PaulDBrown · Accepted solution]

Yes, I see. The variable needs the table context. Try:

Weekly Table =
SUMMARIZE (
    FILTER (
        'Table',
        'Table'[Finished ]
            = CALCULATE ( MAX ( 'Table'[Finished ] ), ALLEXCEPT ( 'Table', 'Table'[Email] ) )
    ),
    'Table'[Email],
    'Table'[Finished ],
    'Table'[Weekly Jobs],
    'Table'[Daily Jobs]
)

[PaulDBrown]

In that case this should work:

Weekly Jobs Table =
SUMMARIZE (
    FILTER (
        'Table',
        'Table'[Finished ]
            = CALCULATE (
                MAX ( 'Table'[Finished ] ),
                ALLEXCEPT ( 'Table', 'Table'[Weekly Jobs] )
            )
    ),
    'Table'[Email],
    'Table'[Finished ],
    'Table'[Weekly Jobs]
)

[HotChilli · Accepted solution]

My interpretation is that @Applicable88 wants the activity, the max date for that activity and the associated email

TableWeekly = 
CALCULATETABLE(
    ADDCOLUMNS ( VALUES(Table1[Weekly Check]),
    "Last Time Checked", CALCULATE ( MAX ( Table1[FinishTime] ) )),
    Table1[Weekly Check] <> blank()
)  

 followed by new column

ColumnE = var _lastTime = TableWeekly[Last Time Checked]
var _checkType = TableWeekly[Weekly Check]
RETURN 
CALCULATE(MIN(Table1[E-Mail]),Table1[FinishTime] = _lastTime && Table1[Weekly Check] = _checkType)

Hopefully getting closer!

[Applicable88]

@HotChilli thank you so much! This indeed is the solution. Also thanks for correcting my interpretation. Maybe I wasn't clear enough. But @HotChilli  or  @PaulDBrown can you  tell me why the original syntax with addcolumns and calculate (context transition) didn't work out in the first place? I'm eager to know what's happening in the filter and row context of that evaluation. Since calculate normally always put the row as a filter. But in that constellation it didn't worked out. 

All the best. 

[PaulDBrown]

Try:

weekly =
VAR _Date = CALCULATE( MAX (Table [Finished]), ALLEXCEPT (Table, Table[email]))

RETURN

SUMMARIZE(FILTER(Table, Table[Finished] = _Date), Table [Finished], Table[email], Table [Weekly Jobs])

[Applicable88]

@PaulDBrown thanks for your help, but its not working out, because this with filter the weekly job with the max finished time, but disregard other "different" weekly jobs. It mus do to the variable which sets, that just one finished time is the right one. What I need is every weekly job, but only the most recent one. 

[PaulDBrown · Accepted solution]

Yes, I see. The variable needs the table context. Try:

Weekly Table =
SUMMARIZE (
    FILTER (
        'Table',
        'Table'[Finished ]
            = CALCULATE ( MAX ( 'Table'[Finished ] ), ALLEXCEPT ( 'Table', 'Table'[Email] ) )
    ),
    'Table'[Email],
    'Table'[Finished ],
    'Table'[Weekly Jobs],
    'Table'[Daily Jobs]
)

[Anonymous]

Weekly = 
CALCULATETABLE(
    SUMMARIZE (
        Table1,
        Table1[Weekly Check],
        "Last Time Checked",MAX(Table1[FinishTime]),
        "E-Mail",CALCULATE(MAX(Table1[E-Mail]),Table1[FinishTime]==max(Table1[FinishTime]))
    ),  
    Table1[Weekly Check]<>blank()
)

Seems to work. I used your test file.

[Applicable88]

Also a big thank to you @PaulDBrown. I just figure out that your approach is also letting me to the wanted outcome as @HotChilli  helped me to clarify. In the allexcept argument I just swapped the E-mail with [Weekly Jobs] and voila, the table only show every weekly job once and only the most recent one with the right email.

Thank you very much. 

Best. 

[HotChilli]

I know it's sample data.  I'm concerned that the DAX posted doesn't work due to syntax errors.

Maybe you can post your sample pbix and I'll look at it.

[Applicable88]

@HotChilli , I could reproduce the same behaviour. Just to explain the situation again:

The calculated table should filter the orginal table and show all weekly tasks, but only those with the newest date. Also show who did it (email adress) and the finish time of it. 

The person with the k@company.com emailadress for example did the last "danger check". But the calculated table with follwing syntax shows the right timestamp but not the right email adress: 

Apparently no context transition is happening here with the email, instead he shows the max letter in alphabeth which is "z". But the context transisiton works on the finished time. 

Calculate table syntax:

Weekly = 
CALCULATETABLE(
    ADDCOLUMNS (
    SUMMARIZE (
        Table1,Table1[Weekly Check]        
    ),
    "Last Time Checked",
        CALCULATE (
            MAX ( Table1[FinishTime] )
        ),
    "E-Mail",
        CALCULATE (
            MAX ( Table1[E-Mail] )
        )
    ),
    Table1[Weekly Check]<>blank()
)        

The Link to the sample files:

https://drive.google.com/drive/folders/1ywoGGzLsdOXzr__gOFsHiMv04lZ7Uzy4?usp=sharing

Hope someone can find the problem here.

Thank you very much in advance.

Best. 

[Applicable88]

@HotChilli thank you. Give me some time to provide sample data. I try to reproduce the problem and then provide download link. 

Best. 

[HotChilli]

Can you double-check the DAX please?  It doesn't work due to syntax errors.

Also, can you change the data on emails please as an experiment? Make the 'john @ email' start with a z

[Applicable88]

@HotChilli I just made up some sample data. In reality in my original data there are of course real email adresses. Its just about the concept why its not working. I think syntax shoudl be right, I copied the original one and just change the columns names according to the sample table.