topic Re: Calculate in selectcolumns on all rows of a user in DAX Commands and Tips

Calculate in selectcolumns on all rows of a user

power_roboter — Thu, 09 Jul 2020 06:58:40 GMT

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

Re: Calculate in selectcolumns on all rows of a user

Anonymous — Thu, 09 Jul 2020 11:25:10 GMT

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!

Re: Calculate in selectcolumns on all rows of a user

power_roboter — Thu, 09 Jul 2020 12:39:30 GMT

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

Re: Calculate in selectcolumns on all rows of a user

AntrikshSharma — Thu, 09 Jul 2020 12:53:50 GMT

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

Re: Calculate in selectcolumns on all rows of a user

power_roboter — Thu, 09 Jul 2020 18:44:59 GMT

Works perfect! Thanks!