Forum Discussion

Noredlac86's avatar
Noredlac86
Icon for Helper I rankHelper I
2 years ago
Solved

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!

 

 

 

Need Help
  • foodd's avatar
    foodd
    2 years ago

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

     

     

     

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • foodd's avatar
    foodd
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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's avatar
      Noredlac86
      Icon for Helper I rankHelper I
      2 years ago

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