Distinct Count with sum conditions

[KamalMalek]

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 which is wrong 

Exp: Rami & Ahmad reapeated multi times "Value =0 and Value >0, so should be excluded from our Measure

i need furmual with Answer =1 , because only user "Wassem" is active and never purchased 

User	status	Purchase value
Rami	Active	0
Ahmad	Active	0
fahad	Not Active	0
wassem	Active	0
Rami	Active	22
Rami	Active	33
Ahmad	Active	50

[Fowmy · Accepted solution]

@KamalMalek 

Use this measure: 

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

[Fowmy · Accepted solution]

@KamalMalek 

Use this measure: 

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

[KamalMalek]

Really appreciate your support thanks a lot 🙂

[Fowmy]

@KamalMalek 

Kinldy accept it as solution if it works for you.

[latimeria · Accepted solution]

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 ) )

[KamalMalek]

Thanks a lot 🙂