Create a (DAX) table to hold count of IDs by team but to also delete duplicates - how to do this?

[D_PBI]

Hi,
I am attempting to create a table, uisng DAX, to return the count of distinct Contributor IDs from multiple CALCULATETABLE results. The result I am looking to obtains is shown in the dummy screenshot below:


How the above table is calculated would be to follow something like the below (note, the below DAX doesn't work - hence asking here):

I'll include the actual text of the above screenshot below for it to be reused in order for your help.

The above doesn't work and certainly the 'RETURN' bit doesn't and I'm lost on how to do it. What I am trying to do is produce a list of Contributor IDs, grouped by the Agreement table and the Team Name from the Team table, with the stated filtering applied. This aim will be completed multiple times shown by the different CALCULATETABLE variables. The list of Contributor IDs from each variable is to be appended, grouped by Team Name and for each Team if there are any duplicated Contributor IDs then for these duplicates to be removed. So in the end, the whole DAX table is to return the table as shown in the first screenshot, which is the count of unique Contributor ID grouped by Team Name.

Please can someone one help me?  Thanks in advance.

Below is the text for the DAX:

Contributors = 
VAR __FYS = __Figures[__FinancialYearStart]
VAR __FYE = __Figures[__FinancialYearEnd]
VAR __PFYS = __Figures[__PreviousFinancialYearStart]
VAR __PFYE = __Figures[__PreviousFinancialYearEnd]

VAR __Fig1_Contributors =
    CALCULATETABLE(
        SUMMARIZE(
            agreement,
            team[Team Name],
            "Contributors", VALUES( case[contributor.contactid] )
        ),
        DATESBETWEEN( agreement[Received Date], __PFYS, __PFYE ),
        OR(
            OR(
                CONTAINSSTRING( agreement[Agreement Status], "Preliminary" ),
                CONTAINSSTRING( agreement[Agreement Status], "Full Application" )
            ),
            CONTAINSSTRING( agreement[Agreement Status], "Submitted" )
        ),
        agreement[Agreement Type] = "Funding - External",
        team[Team Name] IN { "Engineering", "Marketing", "Operations" }
    )

VAR __Fig2_Contributors =
    CALCULATETABLE(
        SUMMARIZE(
            agreement,
            team[Team Name],
            "Contributors", VALUES( case[contributor.contactid] )
        ),
        DATESBETWEEN(agreement[Received Date], __PFYS, __PFYE ),
        DATESBETWEEN(agreement[Execution Date], __FYS, __FYE ),
        agreement[Agreement Type] = "Funding - External",
        team[Team Name] IN { "Engineering", "Marketing", "Operations" }
    )

RETURN
    SUMMARIZE(
        UNION(
            __Fig1_Contributors,
            __Fig2_Contributors
        ),
        team[Team Name]
        ADDCOLUMNS([Contributors])
)

[parry2k]

@D_PBI it is not easy to answer looking at the dax code, you should share sample data (if more than one table then share date model). Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[D_PBI]

@parry2k - fully understood. I hope this adds more to the context.

The relationship model is:

The structure of the team table is:

The structure of the systemuser table is:

The structure of the Agreement table is:

For the __Fig1_Contributors variable I would list the Contactid, grouped by Team Name, with the filtering as stated.

For the __Fig1_Contributors variable I would list the Contactid, groued by Team Name, with the filtering as stated.

The RETURN part of the DAX I would like to return a table that contains two columns 'Team Name' and 'Contactid' - however, the Contactid needs to be de-duplicated. So it will contains a list of unique Contactid grouped by Team Name (so the Contactid can appear more than once in the overall list , but not more than once in the list per Team Name). The below mock-up may help with what I expect the Variables and the Return part to do:

Is this of anymore use?  Are you able to help me with my aim?  Thanks.