Forum Discussion

PowerQueryFTW's avatar
PowerQueryFTW
Frequent Visitor
8 years ago
Solved

Remove Duplicates when the key ID's fall within the same date ranges

I have a table that tracks advertising campaigns.  The table has a key ID column, and then campaign start and end dates.  Is there a way to identify duplicate IDs that are within the same start and ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi PowerQueryFTW

    You could create calculated columns with DAX

    min start date =
CALCULATE (
    MIN ( [start date] ),
    FILTER (
        ALL ( Sheet1 ),
        [keyid] = EARLIER ( [keyid] )
            && [start date] <= EARLIER ( Sheet1[end date] )
            && [end date] >= EARLIER ( [start date] )
    )
)

max end date =
CALCULATE (
    MAX ( [end date] ),
    FILTER (
        ALL ( Sheet1 ),
        [keyid] = EARLIER ( Sheet1[keyid] )
            && [start date] <= EARLIER ( Sheet1[end date] )
            && [end date] >= EARLIER ( [start date] )
    )
)

count of dicuplates =
CALCULATE (
    COUNT ( Sheet1[keyid] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[keyid] ),
        [min start date] = EARLIER ( Sheet1[min start date] )
            && [max end date] = EARLIER ( Sheet1[max end date] )
    )
)

if true = IF([count of dicuplates]>1,"TRUE","FLASE")

     

    Or measures

    min start date1 = CALCULATE(MIN([start date]),FILTER(ALL(Sheet1),[keyid]=MAX([keyid])&&[start date]<=MAX(Sheet1[end date])&&[end date]>=MAX([start date])))

max end date1 = CALCULATE(MAX([end date]),FILTER(ALL(Sheet1),[keyid]=MAX(Sheet1[keyid])&&[start date]<=MAX(Sheet1[end date])&&[end date]>=MAX([start date])))

count of dicuplates1 = CALCULATE(COUNT(Sheet1[keyid]),FILTER(ALLEXCEPT(Sheet1,Sheet1[keyid]),[min start date]=MAX(Sheet1[min start date])&&[max end date]=MAX(Sheet1[max end date])))

if true1 = IF(MAX([count of dicuplates])>1,"TRUE","FLASE")

     

    Best Regards

    Maggie

     

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi PowerQueryFTW

You could create calculated columns with DAX

min start date =
CALCULATE (
    MIN ( [start date] ),
    FILTER (
        ALL ( Sheet1 ),
        [keyid] = EARLIER ( [keyid] )
            && [start date] <= EARLIER ( Sheet1[end date] )
            && [end date] >= EARLIER ( [start date] )
    )
)

max end date =
CALCULATE (
    MAX ( [end date] ),
    FILTER (
        ALL ( Sheet1 ),
        [keyid] = EARLIER ( Sheet1[keyid] )
            && [start date] <= EARLIER ( Sheet1[end date] )
            && [end date] >= EARLIER ( [start date] )
    )
)

count of dicuplates =
CALCULATE (
    COUNT ( Sheet1[keyid] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[keyid] ),
        [min start date] = EARLIER ( Sheet1[min start date] )
            && [max end date] = EARLIER ( Sheet1[max end date] )
    )
)

if true = IF([count of dicuplates]>1,"TRUE","FLASE")

 

Or measures

min start date1 = CALCULATE(MIN([start date]),FILTER(ALL(Sheet1),[keyid]=MAX([keyid])&&[start date]<=MAX(Sheet1[end date])&&[end date]>=MAX([start date])))

max end date1 = CALCULATE(MAX([end date]),FILTER(ALL(Sheet1),[keyid]=MAX(Sheet1[keyid])&&[start date]<=MAX(Sheet1[end date])&&[end date]>=MAX([start date])))

count of dicuplates1 = CALCULATE(COUNT(Sheet1[keyid]),FILTER(ALLEXCEPT(Sheet1,Sheet1[keyid]),[min start date]=MAX(Sheet1[min start date])&&[max end date]=MAX(Sheet1[max end date])))

if true1 = IF(MAX([count of dicuplates])>1,"TRUE","FLASE")

 

Best Regards

Maggie