Forum Discussion

ljx0648's avatar
ljx0648
Icon for Helper III rankHelper III
2 years ago
Solved

Return rows with duplicates based on column status

Hi guys,

 

I have some data and it came from a manual entry CRM so it has alot of human errors. (which you will see in the Customer Stage, the status will be marked as XXX Error)

 

My objective is to filter out the Customer profile which is at the latest stage. 

 

Customer Stage Flow Chart: Lead OR Lead Error -> Prospect OR Prospect Error-> Profile OR Profile Error (final stage)

 

Below is a piece of sample data I have:

 

Customer Profile StatusAsset
ALead 10
AProspect20
BLead 30
BProspect30
BProfile50
CLead 40
CProfile60
CProfile60
DLead 15
DProspect20
DProspect Error20
EProfile 5
EProfile Error5

 

The result I am looking for as per below:

 

Customer Profile StatusAsset
AProspect20
BProfile50
CProfile60
CProfile60
DProspect20
DProspect Error20
EProfile5
EProfile Error5

 

You will probably realize that there are still duplicates after the exercise, however, my goal here is not trying to de-dup but picking up the profile at their latest stages.

 

Lastly, if this can be done in power editor that would be great as I will need to join this table with other tables afterwards.

 

I tried approached this by creating order for the stages (stages 1 to 3), then grouping the customer, afterwards select the Max order desc, but this approach only return ONE record (which I willl need duplicate if it is in the same stage).

 

May I know if someone can help.

 

Any tips are apprecaited.

 

Thank you

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ljx0648 ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create a measure.

    Measure =
VAR _1 =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Customer ] = SELECTEDVALUE ( 'Table'[Customer ] )
        ),
        'Table'[Asset]
    )
RETURN
    IF ( MAX ( 'Table'[Asset] ) = _1, 1, BLANK () )

    Or a column.

    Column =
VAR _1 =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Customer ] = EARLIER ( 'Table'[Customer ] ) ),
        'Table'[Asset]
    )
RETURN
    IF ( ( 'Table'[Asset] ) = _1, 1, BLANK () )

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi ljx0648 ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Create a measure.

    Measure =
VAR _1 =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Customer ] = SELECTEDVALUE ( 'Table'[Customer ] )
        ),
        'Table'[Asset]
    )
RETURN
    IF ( MAX ( 'Table'[Asset] ) = _1, 1, BLANK () )

    Or a column.

    Column =
VAR _1 =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Customer ] = EARLIER ( 'Table'[Customer ] ) ),
        'Table'[Asset]
    )
RETURN
    IF ( ( 'Table'[Asset] ) = _1, 1, BLANK () )

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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