Distinct Count with sum conditions

Dear All, In table down i need to count active users with no purchase at all, when we use Dax Furmula Measue= calculate(distinctcount{"User", Filter(status="Active"&&purcahse value="0") = 3 users w...

Data Protection

Fowmy

2 years ago

KamalMalek

Use this measure:

Never Bought = 
CALCULATE(
    COUNTROWS(
        FILTER(
            DISTINCT( Table10[User] ),
            CALCULATE( SUM(Table10[Purchase value]) ) = 0
        )
    ),

latimeria

2 years ago

Hi KamalMalek ,

Another way:

VAR StatusActiveAndPurchase0 =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( 'Table', 'Table'[Purchase value] = 0 && 'Table'[Status] = "Active" ),
            "@User", 'Table'[User]
        )
    )
VAR STatusActiveAndPurchaseNot0 =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER( 'Table', 'Table'[Purchase value] > 0 && 'Table'[Status] = "Active" ),
            "@User", 'Table'[User]
        )
    )
RETURN
    COUNTROWS( EXCEPT( StatusActiveAndPurchase0, STatusActiveAndPurchaseNot0 ) )

Forum Discussion

Distinct Count with sum conditions

Related content

count distinct

Distinct Count with multiple conditions

Distinct Count

DAX distinct count with filter condition

Conditional Distinct Count of Values