cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculate in selectcolumns on all rows of a user

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II

## Re: Calculate in selectcolumns on all rows of a user

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

Thank you,
Antriksh Sharma
4 REPLIES 4
Highlighted
Super User V

## Re: Calculate in selectcolumns on all rows of a user

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)

Highlighted
Frequent Visitor

## Re: Calculate in selectcolumns on all rows of a user

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

Highlighted
Super User II

## Re: Calculate in selectcolumns on all rows of a user

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

Thank you,
Antriksh Sharma
Highlighted
Frequent Visitor

## Re: Calculate in selectcolumns on all rows of a user

Works perfect! Thanks!

Announcements

#### Microsoft Ignite

This will be a conference that you do not want to miss!

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors