Duplicate detection, return a value

[michael_knight]

Hi,

I'm trying to create a column which will display an outcome depending on the results of the duplicate detection

This is sample data that I'm working with

This is the outcome I want.

The first thing I look at is if Address, Status and Company are all the same. After looking at that we will then look at the Agent, if there is multiple Agents for the same Address then we will display "Half". If there is one or more Address that have the same Agent then we will display "Full"

This is the outcome I got from Power BI. I did a simple Concatenate for the Conc column the merge the columns and then used this DAX formula to return the "Half" or "Full"

Half/Full Appointment = 
Var Appoint = 'Table'[Conc]

var AppCount = 
 CALCULATE(
    COUNTROWS('Table'),
    all('Table'),
    'Table'[Conc] = Appoint
)

return

IF(AppCount <= 1, "Full", "Half") 

Does anyone know how to get the outcome that I'm looking for?

PBI File: https://www.dropbox.com/s/40kthwjmdwufm7l/Appoint.pbix?dl=0

Let me know if you've got any questions

Cheers,

Mike

[mahoneypat · Accepted solution]

Please try this column expression to get your result.

HalfFull =
VAR agentcount =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Agent] ),
        ALLEXCEPT ( 'Table', 'Table'[Address], 'Table'[Status], 'Table'[Company] )
    )
RETURN
    IF ( agentcount = 1, "Full", "Half" )

Pat

[michael_knight]

Thank you, Pat!