Do not count duplicates DAX Formula

[Anonymous]

Hi Everyone,

I am having trouble getting a calculation that works like a COUNTIF in PowerBI. 

If this is my database

and I am looking for and end result like this

In Process	1
Rejected	1
Carrier Invoice Posted in SAP ERP	1

Since Carrier Reference "ALBEMA2-2040596" has duplicates with Life Cycle Status = "Rejected" that i do not need them to be counted.

I currenlty have this DAX formula which is not helping much

CALCULATE(

DISTINCTCOUNT(RawData[Carrier Reference]), FILTER(RawData,RawData[Carrier Reference]=RawData[Carrier Reference]))

[v-luwang-msft]

Hi @Anonymous ,

Due to ALBEMA2-2040596 has Life Cycle Status:Carrier Invoice Posted in SAP ERP，and you just want to know the count for the times it was payed，so ignore   ALBEMA2-2040596 forother  Life Cycle Status ,right? If so ,try the following Steps:

Step1,use the following measure:

test =
VAR Life =
    MAX ( 'RawData'[Life Cycle Status] )
VAR new1 =
    CALCULATE (
        COUNT ( RawData[Carrier Reference] ),
        FILTER (
            ALL ( RawData ),
            RawData[Carrier Reference] = MAX ( RawData[Carrier Reference] )
                && RawData[Life Cycle Status] = "Carrier Invoice Posted in SAP ERP"
        )
    )
VAR NEW2 =
    IF ( NEW1 = 1, "Carrier Invoice Posted in SAP ERP", Life )
RETURN
    NEW2

Step 2,create new column based on test:

TESTCOLUMN =
RawData[test]

Step3, create new measure :

your_measurefinal =
CALCULATE (
    DISTINCTCOUNT ( RawData[Carrier Reference] ),
    FILTER ( ALL ( RawData ), RawData[TESTCOLUMN] = MAX ( RawData[TESTCOLUMN] ) )

final you will see :

Click  here  to download pbix if you need.

Best Regard

Lucien Wang

[BA_Pete]

Hi @Anonymous ,

I may be oversimplifying your scenario, but I believe a simplification of your measure should work fine:

_yourMeasure = DISTINCTCOUNT(RawData[Carrier Reference])

When you apply this using [Life Cycle Status] as a dimension it should give you what you want.

Pete 

[Anonymous]

wow....I am not sure why was i overcomplicating things, i feel dumb!!

[BA_Pete]

Not dumb - I do it ALLLL the time!

Glad it's working for you 🙂

Pete

[Anonymous]

I know why was not working in the beginning. while testing data for example

ALBEMA2-2040596 Rejected status is getting counted and that is exactly what I DO NOT need. Does this make sense?

[BA_Pete]

Not sure.

Do you mean that as soon as ANY [Carrier Reference] is rejected, you don't want any more to be counted?

...or do you mean that you want all others to be counted but not ALBEMA2-2040596 specifically?

...or do you mean it works just fine now, but you found why is wasn't working for you originally?

Pete

[Anonymous]

I found why the simple DISTINCTCOUNT is not working for what I want to achieve.

The problem is that the invoice perhaps was sent twice, it was once rejected and once payed. for this particular analysis i just want to know the count for the times it was payed