cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

New Member

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

 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
2 ACCEPTED SOLUTIONS
Super User

@KamalMalek

Use this measure:

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
Solution Specialist

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 5
Super User

@KamalMalek

Use this measure:

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

Did I answer your question? Mark my post as a solution! and hit thumbs up
New Member

Really appreciate your support thanks a lot 🙂

Super User

@KamalMalek

Kinldy accept it as solution if it works for you.

Did I answer your question? Mark my post as a solution! and hit thumbs up
Solution Specialist

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

New Member

Thanks a lot 🙂

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors