Distinct count of users with net balance > 0

Question

My data looks like this.

I tried with below DAX but I am getting an error:

Distinct Users = CALCULATE(DISTINCTCOUNT(MyList[User]),SUM(MyList[Balance]) > 0)

Error message: A function 'SUM' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

CR7SMS CR7SMS · Accepted Answer · 2020-02-28T15:32:05

You could do this by using a calculated column to evaluate the balance and then use that to calculate the measure:

Column:

Distinct Users Test = 
        VAR User = MyList[User]    
RETURN CALCULATE(SUM(MyList[Balance]),
                FILTER(MyList,MyList[User]=User))

Measure:

Distinct Users = CALCULATE(DISTINCTCOUNT(MyList[User]),
                            MyList[Distinct Users Test]>0)

Hope this helps.

Edit: If you want it as a single measure, You can make use of summarize and calculatetable:

Distinct Users New = COUNTX(
                            CALCULATETABLE(SUMMARIZE(MyList,MyList[User],
                                            "Bal",
                                            SUM(MyList[Balance]))),
                            [Bal]>0)

Distinct count of users with net balance > 0

1 Answers