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 Proce...
Need Help
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