filter table based on duplicate values in selected columns

[Muki]

Hi

I'm trying to find a solution for below situation.

Add a column with amount of duplicate values based upon selected columns ( version, coverage )

For these duplicate records, only keep the records with latest 'starte date' and status 'ok'.

Would really appreciate if you could help me out!

Regards

Muki

[Zubair_Muhammad · Accepted solution]

Hi @Muki

Try this calculated table

Go to Modelling Tab>>>NEW TABLE

Table =
ADDCOLUMNS (
    SUMMARIZE (
        TableName,
        TableName[version],
        TableName[coverage],
        "Start_Date", CALCULATE ( MAX ( TableName[start date] ), TableName[status] = "ok" )
    ),
    "ID", CALCULATE (
        SELECTEDVALUE ( TableName[id] ),
        TableName[start date] = EARLIER ( [Start_Date] )
    ),
    "Status", "Ok",
    "Count", CALCULATE ( COUNT ( TableName[coverage] ) )
)

[Zubair_Muhammad · Accepted solution]

Hi @Muki

Try this calculated table

Go to Modelling Tab>>>NEW TABLE

Table =
ADDCOLUMNS (
    SUMMARIZE (
        TableName,
        TableName[version],
        TableName[coverage],
        "Start_Date", CALCULATE ( MAX ( TableName[start date] ), TableName[status] = "ok" )
    ),
    "ID", CALCULATE (
        SELECTEDVALUE ( TableName[id] ),
        TableName[start date] = EARLIER ( [Start_Date] )
    ),
    "Status", "Ok",
    "Count", CALCULATE ( COUNT ( TableName[coverage] ) )
)

[Zubair_Muhammad · Accepted solution]

@Muki

See the attached file here

[Muki]

Thank you very much Muhammed @Zubair_Muhammad!

[Muki]

Hi @Zubair_Muhammad

I have an additional question about the resolution you provided here.

So in the summary part you filter the table based on version/coverage and the latest start date.

But then as part of 'add columns' you again mention the latest start date? I would like to know whether we can just add columns in our result table (because in my real example I have a lot more columns) without mentioning any expressions.

Basically, filter the table on some columns, create extra calculated columns and take over the values of other columns in my original table.

Is that possible?

thanks!

Muki