CODEWITHSUNDEEP
powerbidax
NewPowerBIUser
NewPowerBIUser

Reputation: 13

DAX measure to count IDs satisfying a threshold condition

SalePersonId    Month   Qty
1               Jan-18  5
2               Jan-18  7
1               Feb-18  1
2               Feb-18  8
3               Feb-18  12

I need to create a measure which gives me a count of salespersons whose total sales quantity is more than 10 for the year 2018.

The result should be 2 (Sale person 1 & 3)

I can achieve this in T-SQL with the following query:

SELECT COUNT(Distinct EmpId) FROM T1 GROUP BY UserId HAVING SUM(Qty) > 10

How can I do the same in DAX?

Upvotes: 1

Views: 1086

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Here's one possible approach:

= COUNTROWS(
      FILTER(
          SUMMARIZECOLUMNS(
              T1[SalePersonId],
              "Total", SUM(T1[Qty])),
      [Total] > 10))

The SUMMARIZECOLUMNS part is essentially

SELECT SalePersonId, SUM(Qty) AS 'Total' FROM T1 GROUP BY SalePersonId

The FILTER part is equivalent to the HAVING clause and then you just count the rows in the resulting table.

Upvotes: 1

Related Questions