Add column in table that checks for distinct records

[jasonyeung87]

Hi,

I have the following table structure:

There is a 1 to many relationship between the Ticket and Time Entries tables (related by the Ticket ID field of both tables). I want to create a new column (named Agent ID) in the Ticket table based on the following logic:

- if there is 1 distinct Responder ID for all related Time Entries, set Agent ID to the Responder ID.

- if there is more than 1 distinct Responder ID for all related Time Entries, set Agent ID to 1.

In the above example, tickets 1 to 3 all have 1 distinct Responder ID's, so the Agent ID is Responder ID. But ticket 4 has 2 distinct Responder ID's (56 and 67), so the Agent ID is 1.

I was wondering if there's a quick way to do this? Ideally it would be done within the data section of Power BI Desktop instead of Power Query Editor.

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in the Tickets table

Column = if(CALCULATE(DISTINCTCOUNT('Time entries'[Respondent ID]),RELATEDTABLE('Time entries'))=BLANK(),BLANK(),if(CALCULATE(DISTINCTCOUNT('Time entries'[Respondent ID]),RELATEDTABLE('Time entries'))=1,Tickets[Respondent ID],1))

Hope this helps.

The next time when you post a question, please share data in a format that can be pasted in an MS Excel file.  Sharing a picure is useless because the person helping you has to wast time in typing the data in MS Excel.