filter Duplicates with 2 columns - HELP!

[Anonymous]

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

[VijayP]

@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 · Accepted solution]

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]

@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]

@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

[v-yiruan-msft]

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]

unfortunately I am not getting any values

[v-yiruan-msft]

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]

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

[v-yiruan-msft]

Hi @Anonymous ,

You can create two measures as below:

Status count per Carref = 
CALCULATE (
    DISTINCTCOUNT ( 'RawData'[Life Cycle Status] ),
    ALLEXCEPT ( 'RawData', 'RawData'[Carrier Reference] )
)

Measure = 
 VAR _tab =
    SUMMARIZE (
        'RawData',
        'RawData'[Carrier Reference],
        RawData[Life Cycle Status],
        "Flag",
            IF (
                 (
                    [Status count per Carref] > 1
                        && SELECTEDVALUE ( 'RawData'[Life Cycle Status] ) = "Carrier Invoice Posted in SAP ERP"
                )
                    || [Status count per Carref] = 1,
                1,
                0
            )
    )
RETURN
    SUMX ( _tab, [Flag] )

Best Regards