Detect and find duplicate values using DAX, Power BI

[Noredlac86]

HI Team.

I have this table:

ReportNo-	-PassPort ID-	-Travel To:-	Only Traver in the last report?
1	84569	Los Angeles
1	214	Texas
1	659	Mexico
2	214	California
2	715	Paris
2	845	Holand
2	456	Phili
3	84569	Los Angeles	Yes
3	84569	Medellin	Yes
3	456	New york	No
3	456	Mexico	No
3	456	Canada	No
3	954526	Colombia	Yes

And i need to know if a passanger have no traveled in the last two reports

I need to compare the last two reports. I mean if my last report is No9. I need to compare No9 and No8 and confirm which passanger in the Report No9 does not show in the report No8

In the example the last report is no3. The passange 84569 does not show in the report No2, therefore i have marked like yes.

The passanger 456, show in the report No2., does means. No

The passanger 954526 Does not show in the report No2, therefore i have marked like yes.

I was wondering if someone can help me with this problem.

Thanks!

[v-cgao-msft]

Hi @Noredlac86 ,

Please try:

Only Travel in Last Report ? = 
VAR __CurrentReportNo = 'Table'[ReportNo]
VAR __CurrentPassportID = 'Table'[PassPort ID]
VAR __PreviousReportNo =
    CALCULATE (
        MAX ( 'Table'[ReportNo] ),
        ALL ( 'Table' ),
        'Table'[ReportNo] < __CurrentReportNo
    )
VAR __MaxReportNo =
    MAX ( 'Table'[ReportNo] )
RETURN
    IF (
        'Table'[ReportNo] = __MaxReportNo,
        IF (
            ISBLANK (
                CALCULATE (
                    COUNT ( 'Table'[PassPort ID] ),
                    ALL ( 'Table' ),
                    'Table'[ReportNo] = __PreviousReportNo,
                    'Table'[PassPort ID] = __CurrentPassportID
                )
            ),
            "Yes",
            "No"
        )
    )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Noredlac86]

thanks so much for your time!

[foodd · Accepted solution]

Try this:

Only Travel in the last report? =
VAR CurrentReport = TravelData[ReportNo]
VAR CurrentPassenger = TravelData[PassPortID]
VAR PreviousReport = CurrentReport - 1
RETURN
    IF (
        COUNTROWS (
            FILTER (
                ALL ( TravelData ),
                TravelData[ReportNo] = PreviousReport
                    && TravelData[PassPortID] = CurrentPassenger
            )
        ) > 0,
        "No",
        "Yes"
    )

[Noredlac86]

omg, wonderfull thanks.!
Simple and it works!
Really appreciate your help!