Forum Discussion

adamlang's avatar
adamlang
Icon for Helper III rankHelper III
2 years ago

Help Debugging a Calculated Column to Find First Project Participated In

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 da...
"DAX"
123abc's avatar
123abc
Icon for Community Champion rankCommunity Champion
2 years ago

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's avatar
adamlang
Icon for Helper III rankHelper III
2 years ago

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's avatar
    123abc
    Icon for Community Champion rankCommunity Champion
    2 years ago

     

    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's avatar
      adamlang
      Icon for Helper III rankHelper III
      2 years ago

      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's avatar
        adamlang
        Icon for Helper III rankHelper III
        2 years ago

        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"
        )
        )