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 duplicates) on my new formula column. The current count of carrier reference, is a simple count.  can anyone guide me to create a DAX measure for this so that I DO NOT count a duplicate carrier reference that has a life cycle status of Rejected

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.

9 Replies

  • amitchandak's avatar
    amitchandak
    Super 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

  • VijayP's avatar
    VijayP
    Community Champion
    5 years ago

    Anonymous 

    Also you can try 

    CALCULATE(

    Count( Life cycle status),
    Life Cycle status = "Carrier Invoice Posted in SAP ERP")

    LEt me know if it solves

    • Anonymous's avatar
      Anonymous
      Not applicable
      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.