Forum Discussion

Anonymous

Not applicable

3 years ago

Summarised table by dates - Max date not quite right

Hello! I have an existing table with customers, they all have one unique tenancy reference but each customer has multiple entries under different jobs. These jobs have unique job numbers and all...

Need Help

Tips and Tricks

Anonymous

Not applicable

3 years ago

Well this is embarassing 😐

That makes sense.

I have an issue the communication table does not hold ANY identifying for the tenancy / customer. It has a unique code for the job but I have to use a third table which links that code to the tenancy reference and then pick the tenancy reference up from the tenancy table. So 3 tables in total and I cant do a direct link between the two i need.

Other than creating a new SQL dim table, can I do something in desktop to create a table with the relevant columns from all 3 tables to combine them all in one place and then summarise? Long winded but would it work in desktop that way?

johnt75

Super User

3 years ago

You can move filters around using TREATAS, e.g.

Last CRM =
SELECTCOLUMNS (
    SUMMARIZE (
        'OpenHousing dbo_re_tenancy',
        'OpenHousing dbo_re_tenancy'[tenancy_ref]
    ),
    "Tenancy ref", 'OpenHousing dbo_re_tenancy'[tenancy_ref],
    "Date",
        VAR Jobs =
            CALCULATETABLE ( VALUES ( 'Jobs'[Job ID] ) )
        RETURN
            CALCULATE (
                MAX ( 'OpenHousing dbo_em_communication'[logged_date] ),
                TREATAS ( Jobs, 'OpenHousing dbo_em_communication'[Job ID] )
            )
)

This assumes that tenancy filters jobs and that there is a corresponding job ID column in communications