cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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]))
))

 

 

Unbenannt.PNG

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
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

View solution in original post

4 REPLIES 4
Highlighted
Super User V
Super User V

Re: Calculate in selectcolumns on all rows of a user

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!

 

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.

 

Unbenannt_2.PNG

Highlighted
Super User II
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

View solution in original post

Highlighted
Frequent Visitor

Re: Calculate in selectcolumns on all rows of a user

Works perfect! Thanks!

Helpful resources

Announcements
Ignite

Microsoft Ignite

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

Get Ready for Power BI Dev Camp

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