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 data model looks  something like this:

 

Contacts Table:

[Client Number][New Calulated Column - First Project Participated]

C-xxxxx1

1
C-xxxxx22
C-xxxxx34
C-xxxxx4Project Not Yet Attended (i.e. no appointment)

 

Pre_Project:

Pre_NameService
1A
2A
3B
4C

 

 

Appointments:

[Client Number]  [Date] [Project]

C-xxxxx1

01/01/20231
C-xxxxx102/01/20232
C-xxxxx103/01/20233
C-xxxxx202/01/20231
C-xxxxx201/01/20232
C-xxxxx203/01/20233
Cxxxxxx305/01/20234

 

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

"DAX"

7 Replies

  • 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

    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