Merge and duplicate flag in powerbi

[Anonymous]

I have a report that gets updated on a monthly basis in power biwhich determines how many issues have been repeated, how many are new and how many are closed. Currently since multiple conditions needs to be considered prior to figuring out if the issue has been repeated or not. So I used the Merged option to get one column. 

In the example below. It shows that row 1 is repeated in row 5(note that only the month has changed) so it means this is an existing issue, Rows 3 and 4 were not present in Sep meaning they are new issues. Row 2 is not repeated in Oct month meaning the issue was resolved.

How can I do this for 1000+ rows, please help

Column1	Column2	Column3	Column4	Column5	Column6	Merged	Status
ABC	Sep	20	10.15.234.123	Critical	The port is not reachable	ABC,20,10.15.234.123,Critical,The port is not reachable	Existing issue
DEF	Sep	45	10.22.235.245	None	The device is down	DEF,45,10.22.235.245,None,The device is down	Closed issue
GEF	Oct	67	11.234.25.56	High	Access denied	GEF,67,11.234.25.56,High,Access denied	New issue
HIG	Oct	845	10.65.34.265	Medium	Issue with the knob	HIG,845,10.65.34.265,Medium,Issue with the knob	New Issue
ABC	Oct	20	12.15.234.123	Critical	The port is not reachable	ABC,20,10.15.234.123,Critical,The port is not reachable	Existing issue

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

You need to build a Month Table relate two tables between month column and column2.

Month Table:

Add a MonthNo column in Fact Table.

MonthNo = RELATED('Month'[MonthNo])

Then build Flag column.

Flag = 
VAR _Count =
    COUNTAX (
        FILTER ( 'Table', 'Table'[Merged] = EARLIER ( 'Table'[Merged] ) ),
        'Table'[Merged]
    )
VAR _MaxMonthNo =
    MAX ( 'Table'[MonthNo] )
VAR _MaxMonthNoPerMerge =
    MAXX (
        FILTER ( 'Table', 'Table'[Merged] = EARLIER ( 'Table'[Merged] ) ),
        'Table'[MonthNo]
    )
RETURN
    IF (
        _Count = 1
            && _MaxMonthNoPerMerge = _MaxMonthNo,
        "New Issue",
        IF ( _MaxMonthNoPerMerge = _MaxMonthNo, "Existing issue", "Closed issue" )
    )

Result is as below.

You can download my pbix file from this link: File

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@Anonymous , Not very clear.

Try a new column

new column =
if(isblank(countx(filter(Table, [Column1] = earlier([Column1]) && [Column2] <earlier([Column2])),[Column1])), "New Issue", "Existing issue")

Column2 need to be a date column or month order