Help Debugging a Calculated Column to Find First Project Participated In

[adamlang]

Hi there,

I'm hoping for some help debugging the following DAX for a Calulated Column in my Contacts Table.

I'm trying to generated the first project that each client engaged with.

The data model looks  something like this:

Contacts Table:

[Client Number]	[New Calulated Column - First Project Participated]
C-xxxxx1	1
C-xxxxx2	2
C-xxxxx3	4
C-xxxxx4	Project Not Yet Attended (i.e. no appointment)

Pre_Project:

Pre_Name	Service
1	A
2	A
3	B
4	C

Appointments:

[Client Number]	[Date]	[Project]
C-xxxxx1	01/01/2023	1
C-xxxxx1	02/01/2023	2
C-xxxxx1	03/01/2023	3
C-xxxxx2	02/01/2023	1
C-xxxxx2	01/01/2023	2
C-xxxxx2	03/01/2023	3
Cxxxxxx3	05/01/2023	4

I also have a Calendar Table, called 'Calendar'. Hopefully the relationships are clear/ straightforward.

Using this video as a guide

What Is The First Product A Customer Purchased? - Power BI Analytics - YouTube

I've incorporated the following DAX into my file,  turning the Measure into a Calulated Column by adding a CALCULATE fuction at the start:   

First Project Participated =
CALCULATE (
    MAXX (
        TOPN (
            1,
            SUMMARIZE ( Appointment, 'Calendar'[Date], 'pre_project (2)'[pre_name] ),
            CALCULATE ( MIN ( 'Calendar'[Date] ) ), ASC
        ),
        'pre_project (2)'[pre_name]
    )
)

It doesn't generate an error, however it is returning false positives for clients that have no appointments. Possibly the Row Context , in the calculated column isn't working as I'd want it to, I'm not sure?

Once I've sorted out this issue, I need the Dax to look across 3 more activities tables (covering: Workshops, Project Enrolments, Visits) which also have the Client Number, a Date, and the Project name. Ultimatly I want the new column search across the four tables, find the earliest interaction, and report back the project name of that interaction.

Thanks,

Adam

[adamlang]

Hi All,

I'm still trying to debug this calculated column, with much thanks to 123abc who has pointed me in a good direction. and I think its nearly there.

I've also now created a demo dataset and .pbix file to explain better what's going on, and the result I need:

https://www.dropbox.com/scl/fo/ys84v81p0k4nms68r4mxy/h?rlkey=8u3rrcg7vax8q41ggimrignli&dl=0

There are two Calculated Columns in the Contact table. I think somewhere in the Dax, part of the code is sorting the list of activities alphabetically by the project name rather than the date, this is returning the wrong result.

For example, for URN01, the first project attended should be RRR (The Visit on 01/01/2023) not AAA, and the most recent project should be AAA, the Appointment on 31/12/2023, not AAA. The MINX or maybe the TOPN function I think its sorting by the project column, and passing the wrong data to the MIN (Calendar [Date]) part of the code.

Anyway that's my best guess of what's happening. I don't know Dax well enough to fix it however, and it might be something else anyway.

Just to add. For the Coloumn [Most Recent Project Participated] there's some added issues. I need it to use the inactive calendar relationship to 'Project_Enrollment'[Finish Date]. Sometimes this will be blank, i.e. if the Enrollment is still active, in which case it should use the active date relationship to 'Project_Enrollment'[Start Date].

Finally, I would like to have these columns in the Contact_Fact table, but that means navigating a inactive (bi-directional) relationship to the contact table - where all the URN references link to. Again not really sure how to work that in.

Many thanks, apprecaite any further support.

Adam

[123abc]

Happy birthday to your calculated column! Let's try to get it working for you.

It seems like the issue might be related to how the calculated column is handling cases where there are no appointments for a client. To handle this, you might want to check if the client has any appointments before calculating the first project.

Here's a modified version of your DAX formula:

First Project Participated =
CALCULATE (
IF (
COUNTROWS (
RELATEDTABLE('Appointments')
) > 0,
MAXX (
TOPN (
1,
SUMMARIZE ( RELATEDTABLE('Appointments'), 'Calendar'[Date], 'pre_project (2)'[pre_name] ),
CALCULATE ( MIN ( 'Calendar'[Date] ) ), ASC
),
'pre_project (2)'[pre_name]
),
"No Appointment"
)
)

This modification checks if there are any appointments for the client. If there are, it proceeds with the calculation as before. If not, it returns a string like "No Appointment" (you can customize this string based on your preference).

As for extending this across 3 more activities tables, you would need to modify the formula to consider each of those tables. One way to approach this is to create a union of all these tables and then use that as a basis for your calculation. However, this might get complex, and it's crucial to ensure that the relationships between these tables are well-defined.

Here's a simplified example assuming a similar structure for other activities tables:

First Project Participated =
CALCULATE (
IF (
COUNTROWS (
UNION (
VALUES('Appointments'[Client Number]),
VALUES('Workshops'[Client Number]),
VALUES('Project Enrolments'[Client Number]),
VALUES('Visits'[Client Number])
)
) > 0,
MAXX (
TOPN (
1,
SUMMARIZE (
UNION (
RELATEDTABLE('Appointments'),
RELATEDTABLE('Workshops'),
RELATEDTABLE('Project Enrolments'),
RELATEDTABLE('Visits')
),
'Calendar'[Date],
'pre_project (2)'[pre_name]
),
CALCULATE ( MIN ( 'Calendar'[Date] ) ), ASC
),
'pre_project (2)'[pre_name]
),
"No Activity"
)
)

This example assumes similar column names and structures for other activities tables. Please adjust accordingly based on your actual data model.

Feel free to ask if you have further questions or if you need additional adjustments!

[adamlang]

Hi @123abc  thanks so much - I'm sure this is the right approach.

The Dax worked fine for the Appointments. But as you mentioned the structure of the other tables is different.

My incorporated Dax looks like the following:

I get an error message saying "Each argument of UNION must have the same number of columns".

First Project Participated =

CALCULATE (

    IF (

        COUNTROWS (

        UNION (

                VALUES('Appointment'[Contact.ccl3030_uniquecrmnumber]),

                VALUES('pre_workshopattendance'[Contact.ccl3030_uniquecrmnumber]),

                VALUES('pre_projectenrolment'[Contact.ccl3030_uniquecrmnumber]),

                VALUES('new_connectionzonevisits'[Contact.ccl3030_uniquecrmnumber])

        )

        ) > 0,

        MAXX (

            TOPN (

            1,

        SUMMARIZE (

            UNION (

                RELATEDTABLE('Appointment'),

                RELATEDTABLE('pre_workshopattendance'),

                RELATEDTABLE('pre_projectenrolment'),

                RELATEDTABLE('new_connectionzonevisits')

            ),

        'Calendar'[Date],

        'pre_project (2)'[pre_name]

        ),

        CALCULATE ( MIN ( 'Calendar'[Date] ) ), ASC

        ),

        'pre_project (2)'

        ),

        "Not Yet Joined A Project"

        )

    )

The additional tables are structured as follows, with the dates linked to the Calendar Table, but they all have different additional columns:

pre_workshopattendance:

Client No. = Contact.ccl3030_uniquecrmnumber

Date = pre_workshopdate

Project = pre_project (2).pre_name

new_connectionzonevisits:

Client No. = Contact.ccl3030_uniquecrmnumber

Date = new_timearived

Project = Service

pre_projectenrolment:

Client No. = Contact.ccl3030_uniquecrmnumber

Date = pre_startdate

Project = pre_project (2).pre_name

Let me know if I can provide further infomation.

Thanks again for helping.

Adam

[123abc]

see, thanks for providing additional details about the structure of the other tables. Since the tables have different additional columns, we need to make sure that the columns used in the SUMMARIZE function have the same structure across all tables involved in the UNION. In your case, it seems like the 'pre_project (2)'[pre_name] column is common across all tables, so we can use that as the basis for the SUMMARIZE function.

Here's the modified DAX expression:

First Project Participated =
CALCULATE (
IF (
COUNTROWS (
UNION (
VALUES('Appointment'[Contact.ccl3030_uniquecrmnumber]),
VALUES('pre_workshopattendance'[Contact.ccl3030_uniquecrmnumber]),
VALUES('pre_projectenrolment'[Contact.ccl3030_uniquecrmnumber]),
VALUES('new_connectionzonevisits'[Contact.ccl3030_uniquecrmnumber])
)
) > 0,
MAXX (
TOPN (
1,
SUMMARIZE (
UNION (
ADDCOLUMNS(RELATEDTABLE('Appointment'), "Project", 'pre_project (2)'[pre_name]),
ADDCOLUMNS(RELATEDTABLE('pre_workshopattendance'), "Project", 'pre_project (2)'[pre_name]),
ADDCOLUMNS(RELATEDTABLE('pre_projectenrolment'), "Project", 'pre_project (2)'[pre_name]),
ADDCOLUMNS(RELATEDTABLE('new_connectionzonevisits'), "Project", 'Service')
),
'Calendar'[Date],
'Project'
),
CALCULATE ( MIN ( 'Calendar'[Date] ) ), ASC
),
'Project'
),
"Not Yet Joined A Project"
)
)

In this modification:

1. I added the "Project" column to each table using the 'pre_project (2)'[pre_name] or 'Service' column as appropriate.
2. The SUMMARIZE function now uses 'Project' as the common column for summarization across all tables.
3. The MAXX function and subsequent calculations use 'Project' as the reference for finding the earliest interaction.

This should address the "Each argument of UNION must have the same number of columns" error. Make sure to replace 'Service' with the actual column name if it differs in your 'new_connectionzonevisits' table.

[adamlang]

Thanks @123abc,

The 'Project' field seems to be throwing up the same error.

I tried changing the [Service] field in Connectionzonevisits to [pre_project (2).pre_name] to match those in the other activities tables, and the two 'Project' lines were underlined in red showing an error, so i played around a bit.

The amended the code is as follows:

First Project Participated =

CALCULATE (

IF (

COUNTROWS (

UNION (

VALUES('Appointment'[Contact.ccl3030_uniquecrmnumber]),

VALUES('pre_workshopattendance'[Contact.ccl3030_uniquecrmnumber]),

VALUES('pre_projectenrolment'[Contact.ccl3030_uniquecrmnumber]),

VALUES('new_connectionzonevisits'[Contact.ccl3030_uniquecrmnumber])

)

) > 0,

MAXX (

TOPN (

1,

SUMMARIZE (

UNION (

ADDCOLUMNS(RELATEDTABLE('Appointment'), "Project", 'Appointment'[pre_project (2).pre_name]),

ADDCOLUMNS(RELATEDTABLE('pre_workshopattendance'), "Project", 'pre_workshopattendance'[pre_project (2).pre_name]),

ADDCOLUMNS(RELATEDTABLE('pre_projectenrolment'), "Project", 'pre_projectenrolment'[pre_project (2).pre_name]),

ADDCOLUMNS(RELATEDTABLE('new_connectionzonevisits'), "Project", 'new_connectionzonevisits'[pre_project (2).pre_name])

),

'Calendar'[Date],

'pre_project (2)'[pre_name]

),

CALCULATE ( MIN ( 'Calendar'[Date] ) ), ASC

),

'pre_project (2)'

),

"Not Yet Joined A Project"

)

)

Its throwing up an error that says Union must have the same number of columns, still.

I assume the 'Project' lines were to to access the virtual table from the ADDCOLUMNS in the formula rather than one of my actual tables? It showed an error also - with [Project], and a Syntex error with just 'Project'

Just for clarity 'pre_project (2)' is the name of my dimention table that contains a list of all the projects and their correstponding Service (the team name effectively). It also might be relevent to say that the [pre_project (2).pre_name] field in the activity tables e.g.'pre_workshopattendance'[pre_project (2).pre_name], is a meged column from the project table.

The relationship that joins the activity tables to the project table is through a key:

Many to 1 

'Appointment'[pre_project]

'pre_workshopattendance' [bbbc_project]

'pre_projectenrolment'[pre_project]                                       }          'pre_project (2)' [pre_projectid]

'new_connectionzonevisits'[pre_projectid]

Hope that makes sense. Grateful if you could take another look, please.

Adam

[adamlang]

Also tried using SELECTCOLUMN, as in the DAX below.

Which passes error free, and returns some results that look ok, but I'm finding lots of instances of a return of "Not Yet Joined A Project", for someone that has had an activity, and it should be returning a project name.

First Project Participated =

CALCULATE (

    IF (

        COUNTROWS (

                UNION (

                    VALUES('Appointment'[Contact.ccl3030_uniquecrmnumber]),

                    VALUES('pre_workshopattendance'[Contact.ccl3030_uniquecrmnumber]),

                    VALUES('pre_projectenrolment'[Contact.ccl3030_uniquecrmnumber]),

                    VALUES('new_connectionzonevisits'[Contact.ccl3030_uniquecrmnumber])

                    )

                ) > 0,

        MAXX (

        TOPN (

                1,

            SUMMARIZE (

                UNION (

                    SELECTCOLUMNS(RELATEDTABLE('Appointment'), "Project", [pre_project (2).pre_name]),

                    SELECTCOLUMNS(RELATEDTABLE('pre_workshopattendance'), "Project", [pre_project (2).pre_name]),

                    SELECTCOLUMNS(RELATEDTABLE('pre_projectenrolment'), "Project", [pre_project (2).pre_name]),

                    SELECTCOLUMNS(RELATEDTABLE('new_connectionzonevisits'), "Project", [Service])

                    ),

                [Project]

                 ),

            CALCULATE ( MIN ( 'Calendar'[Date] ) ), ASC

            ),

            [Project]

    ),

    "Not Yet Joined A Project"

)

)

[adamlang]

One more thing, I forgot about which I realise now is very relevent. Sorry about this.

The new calulated column is in a table called 'Contact Fact' this is a helper table for my main contacts table. 'Contact Fact' holds all the date related information about contacts and is connected to the date table. I wasn't able to connect 'Contacts' directly to the date table without throwing up ambiguity errors.

'Contact Fact' is connected to 'Contact' by way of a one-to-one, bi directional relationship - which is inactive. Activating it would introduce ambiguity between other tables.

Importantly it is the main 'Contact' table through which all the activity tables connect, using a Contactid Key. The say key is used in 'Contact Fact' hence the one to one relationship. 

Realising this, I've moved the calulated column into the 'Contact' table and it appears to be working expected. - So Success, sort of.

I do actually need this new column in the Contact Fact table, as its the client registration date I need to reference in visualisations. So how to activate this inactive relationship in the Dax? I know I need to use USERELATIONSHIP somewhere, not not sure where.

I suppose, now I have the field in 'Contact' I could use a merge query to move it to 'Contact Fact' but that would be a bit wasteful to have the two columns, and seems almost cheating anyway.

I also need to adapt the DAX, for another column, to show not the first project someone has engaged with, but the most recent project they have. I thought it would be just a case of changing the MAX to MIN, but that doesn't seem to be having the right effect. Any thoughts?

Thanks in advance for any further support offered.

Adam