Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

filter Duplicates with 2 columns - HELP!

Hi,   Lets say I have this Data       I need to only count the Carrier Reference that has a status of "Carrier Invoice Posted" in my final numbers, so that i can show true #s (no du...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    thank you for the Reply Vijay,

     

     

    I think I posted my question incorrectly if a carrier reference has a duplicate with a LifeCycleStatus = Reject & LifeCycleStatus = Carrier Invoice posted, then I only want to count the reference of the carrier invoice posted status.

     

    Not sure if this can be obtained with a DAX formula or a step in the transform data.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

amitchandak this is defenitely going in the right direction.

 

The idea is to get a distinct count of all carrier reference, making sure that there are no carrier reference duplicates with 2 different life cycle Status.

 

The new Formula column is the formuala that you entered previously 

 

New Formula = CALCULATE(
DISTINCTCOUNT(RawData[Carrier Reference]), FILTER(RawData,SEARCH("Carrier Invoice Posted in SAP ERP",RawData[Life Cycle Status],,0)>0))

 

I hope this image helps

 

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

You can update the formula of measure [New Formula] as below:

New Formula =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'RawData'[Life Cycle Status] ),
        FILTER (
            ALLSELECTED ( 'RawData' ),
            'RawData'[Carrier Reference] = SELECTEDVALUE ( 'RawData'[Carrier Reference] )
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'RawData'[Carrier Reference] ),
        FILTER (
            'RawData',
            SEARCH ( "Carrier Invoice Posted in SAP ERP", RawData[Life Cycle Status],, 0 ) > 0
                && _count = 1
        )
    )

Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    unfortunately I am not getting any values

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous ，

      The measure which I provided is to get the count of Carrier Reference which only have one Life Cycle Status with "Carrier Invoice Posted in SAP ERP". I'm not sure whether I misunderstanding your requirement... Could you please make example to explain your desired result? What do you mean about count with no carrier reference duplicate? For example: the data in below table, what's your expected result?

      Invoice Carrier Reference Life Cycle Status
      43 ALB596 Carrier Invoice Posted in SAP ERP
      59 ALB596 Rejected
      44 ALB602 Carrier Invoice Posted in SAP ERP
      74 ALB602 Rejected
      76 ALB603 Carrier Invoice Posted in SAP ERP
      58 ALB604 Rejected
      56 ALB604 Carrier Invoice Posted in SAP ERP
      12 ALB604 In Process

      Best Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi, Thank you for replying. If I where to have this data set

         

        My expected result for this data set would be

        Ready for Posting - 0
        In Process - 1
        Carrier Invoice posted in SAP ERP - 4
        Rejected - 1