Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Do not count duplicates DAX Formula

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 Process1
Rejected1
Carrier Invoice Posted in SAP ERP1

 

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]))
Need Help

7 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • BA_Pete's avatar
        BA_Pete
        Super User
        5 years ago

        Not dumb - I do it ALLLL the time!

        Glad it's working for you 🙂

         

        Pete

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    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