Calculate in selectcolumns on all rows of a user

[power_roboter]

Hi dax experts,

I have a table with user log data and want to calculate the numberOfDays for the filtered users inside a selectcolumns. The problem is, that calculate(DISTINCTCOUNT('∑ Tracking Data'[Date_Key]) is always 1 for every user. It seems that calculate always refers to only one row of a user. But it must refer to all rows of a user. How can I implement this?

EVALUATE

DISTINCT (
SELECTCOLUMNS (
FILTER (
'∑ Tracking Data',
'∑ Tracking Data'[Application] = "product_a"
&& '∑ Tracking Data'[Userrole] = "customer"
),
"userID", '∑ Tracking Data'[userId],
"numberOfDays", calculate(DISTINCTCOUNT('∑ Tracking Data'[Date_Key]))
))

[harshnathani]

Hi @power_roboter ,

Try ALL()

DISTINCT (
    SELECTCOLUMNS (
        FILTER (
            '∑ Tracking Data',
            '∑ Tracking Data'[Application] = "product_a"
                && '∑ Tracking Data'[Userrole] = "customer"
        ),
        "userID", '∑ Tracking Data'[userId],
        "numberOfDays", CALCULATE (
            DISTINCTCOUNT ( '∑ Tracking Data'[Date_Key] ),
            ALL ( '∑ Tracking Data' )
        )
    )
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[power_roboter]

This is not working. The calculation then refers to all user data, but not to the data of a (one) user.

[AntrikshSharma · Accepted solution]

maybe try ALLEXCEPT ( '∑ Tracking Data', '∑ Tracking Data'[Userrole] )

[power_roboter]

Works perfect! Thanks!