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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Try a measure like

 

calculate(distinctcount(Table[Carrier Reference]), filter(Table, search("Carrier Invoice Posted", Table[Life Cycle Status],,0)>0))

calculate(count(Table[Carrier Reference]), filter(Table, search("Carrier Invoice Posted", Table[Life Cycle Status],,0)>0))

  • 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