DAX Challenge - Count of Unique ID's where their Sum of another value is >0

[cadalano]

I have a problem which I'm currently able to solve in SQL, but I would like to find a way to accomplish the same calculation using DAX against the data. I basically need to get a Distinct Count of ID's where the sum of another value in the data (per ID) is greater than zero.

if there was a DISTINCTCOUNTX function in DAX, it seems like that would be useful here. Unfortunately there is not - anyone familiar with this problem or a solution?

Here is sample SQL code demonstrating the issue, and how I solve it in SQL

-- Assume #tmpData is a list of Persons who, for particular periods,
-- were paid amounts which subsequently could be negated on separate lines in the table.
--
-- sample data table create & populate below....
IF OBJECT_ID(N'tempdb..#tmpData') IS NOT NULL
BEGIN
DROP TABLE #tmpData
END

SELECT 12345 personID, 'February' allocationPeriod, 4.50 allocationAmount INTO #tmpData UNION ALL
-- person 12345 has their amount negated for February, but it is replaced
SELECT 12345 personID, 'February' allocationPeriod, -4.50 allocationAmount UNION all
SELECT 12345 personID, 'February' allocationPeriod, 3.50 allocationAmount UNION all
SELECT 12345 personID, 'March' allocationPeriod, 3.50 allocationAmount UNION ALL

-- person 23456 has payments for Feb and March. Nothing special removed.
SELECT 23456 personID, 'February' allocationPeriod, 3.50 allocationAmount UNION all
SELECT 23456 personID, 'March' allocationPeriod, 3.50 allocationAmount UNION ALL

-- person 34567 has their amount negated and NOT replaced for February
SELECT 34567 personID, 'February' allocationPeriod, 3.50 allocationAmount UNION ALL
SELECT 34567 personID, 'February' allocationPeriod, -3.50 allocationAmount UNION all
SELECT 34567 personID, 'March' allocationPeriod, 3.50 allocationAmount

-- Need Count of Unique persons who ultimately were paid something (allocationAmount>0), per period.
-- calculation example as tSQL - I'm trying to find a way to do this in DAX as a measure
SELECT
-- evaluate if person's amount sum is greater than zero. Consider that person to be a "paid" person if so.
COUNT( DISTINCT
IIF(jPersonPeriodSums.allocationAmount_Sum > 0
, jPersonPeriodSums.personID
, NULL)
) paidPersons
, jPersonPeriodSums.allocationPeriod
FROM
(
--subquery to get sum per person, per period
SELECT
SUM( allocationAmount ) allocationAmount_Sum
, personID
, allocationPeriod
FROM #tmpData
GROUP BY
personID
, allocationPeriod
) jPersonPeriodSums
GROUP BY
jPersonPeriodSums.allocationPeriod

[Anonymous]

Try this

Measure =
COUNTROWS (
DISTINCT ( CALCULATETABLE ( VALUES ( Table[Id] ), Table[FilterValue] > 0 ) )
)

[cadalano]

Thanks ThomasFoster - that seems very promising, however, I think it sticks on the last bit of your Formula

COUNTROWS (
DISTINCT ( CALCULATETABLE ( VALUES ( Table[Id] ), Table[FilterValue] > 0 ) )
)

The highlighted section only seems to work if youre referencing a column value - in my case, I need to reference an aggregate.. The Sum of the amount

Something like this:

COUNTROWS (
DISTINCT ( CALCULATETABLE ( VALUES ( Table[Id] ), Sum(Table[FilterValue]) > 0 ) )
)

But, that formula gives me an error:

"SUM has been used in a True/False expression that is used as a table filter expression. This is not allowed"

[cadalano]

This appears to work exactly as I needed - thank you Owen!

[OwenAuger · Accepted solution]

@cadalano 

I would normally write such a measure as

=
COUNTROWS (
    FILTER (
        VALUES ( Table[id] ),
        CALCULATE ( SUM ( Table[FilterValue] ) ) > 0
    )
)

Regards,

Owen