Forum Discussion

Anonymous's avatar
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
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

Try

Last CRM =
SELECTCOLUMNS (
    SUMMARIZE (
        'OpenHousing dbo_re_tenancy',
        'OpenHousing dbo_re_tenancy'[tenancy_ref]
    ),
    "Tenancy ref", 'OpenHousing dbo_re_tenancy'[tenancy_ref],
    "Date", CALCULATE ( MAX ( 'OpenHousing dbo_re_tenancy'[logged_date] ) )
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi, thanks, I've tried that but it's still returning the same date for each as opposed to the individual last date per tenancy. 

The tenancy and the logged date are on different tables - (re_tenancy and dbo_em_communication) is this causing the issue? 

 

 

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    For that to work there would need to be a relationship between the two tables so that tenancy filters communications.

    • Anonymous's avatar
      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's avatar
        johnt75
        Icon for Super User rankSuper 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