Forum Discussion

KamalMalek's avatar
KamalMalek
New Member
2 years ago
Solved

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

UserstatusPurchase value
RamiActive0
AhmadActive0
fahadNot Active0
wassemActive0
RamiActive22
RamiActive33
AhmadActive50
Data Protection
  • Fowmy's avatar
    Fowmy
    2 years ago

    KamalMalek 

    Use this measure: 

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

     



     

  • latimeria's avatar
    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 ) )

     

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    KamalMalek 

    Use this measure: 

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

     



     

    • latimeria's avatar
      latimeria
      Solution Specialist
      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 ) )