Reply
Noredlac86
Helper I
Helper I
Partially syndicated - Outbound

Detect and find duplicate values using DAX, Power BI

HI Team.


I have this table:

 

ReportNo--PassPort ID--Travel To:-Only Traver in the last report?
184569Los Angeles 
1214Texas 
1659Mexico 
2214California 
2715Paris 
2845Holand 
2456Phili 
384569Los AngelesYes
384569MedellinYes
3456New yorkNo
3456MexicoNo
3456CanadaNo
3954526ColombiaYes

 

 

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!

 

 

 

1 ACCEPTED SOLUTION
foodd
Super User
Super User

Syndicated - Outbound

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"
    )

 

 

foodd_1-1695080122710.png

 

 

 

View solution in original post

4 REPLIES 4
avatar user
Anonymous
Not applicable

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"
        )
    )

vcgaomsft_0-1695192971333.png

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

thanks so much for your time!

foodd
Super User
Super User

Syndicated - Outbound

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"
    )

 

 

foodd_1-1695080122710.png

 

 

 

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

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)