Sum a column based grouping by another one, but respecting user filters

[jrbmendes]

I am new to DAX and I am depressed by how hard it is.

I need to do a very, VERY simple thing that can be summarized as "I want to create measures that count a column grouped by   another one, but that respect the filters already in place by the user."

Let me explain it in a toy model (I do not post the real one because it is in Portuguese).

I have a fact table about user activity, a dimension table of users, and a dimension table of customers.

                   customer 1- * fact_activities *-1users

There is also an activity-type table.

                   activity_type 1-* fact_activities  

The user may filter by activity date, activity type, customer_type, and others. Some filters are on fact_activities, but also on the other tables.

I need things like: activities per user, activities per customer, activities per user per customer, etc.

I have tried many things. One example. This is an attempted measure on users.
I hoped that KEEPFILTERS would, you know, keep the filters, but the measure ignores user filters:

activities_per_user =

VAR _user_sk = dim_user[sk_user]

RETURN

COUNTX(   

               FILTER(  KEEPFILTERS(fact_activities),

                             fact_activities[sk_user] = _user_sk ),

               fact_activities[sk_activity]

)

Please help,

[Sahir_Maharaj]

Hope this helps 🙂

[Sahir_Maharaj]

Activities per Customer per Activity Type = CALCULATE(COUNT(fact_activities[sk_activity]), FILTER(ALL(dim_customer), dim_customer[sk_customer] = fact_activities[sk_customer]), FILTER(ALL(activity_type), activity_type[sk_activity_type] = fact_activities[sk_activity_type]))

These formulas will give you the required results while preserving the filters.

[Sahir_Maharaj]

Activities per User per Activity Type = CALCULATE(COUNT(fact_activities[sk_activity]), FILTER(ALL(dim_user), dim_user[sk_user] = fact_activities[sk_user]), FILTER(ALL(activity_type), activity_type[sk_activity_type] = fact_activities[sk_activity_type]))

[Sahir_Maharaj]

3. Activities per User per Customer = CALCULATE(COUNT(fact_activities[sk_activity]), FILTER(ALL(dim_user), dim_user[sk_user] = fact_activities[sk_user]), FILTER(ALL(dim_customer), dim_customer[sk_customer] = fact_activities[sk_customer]))

[Sahir_Maharaj]

2. Activities per Customer = CALCULATE(COUNT(fact_activities[sk_activity]), FILTER(ALL(dim_customer), dim_customer[sk_customer] = fact_activities[sk_customer]))

[Sahir_Maharaj]

Hi @jrbmendes 

To solve your problem, you can try using the DAX formula below:

1. Activities per User = CALCULATE(COUNT(fact_activities[sk_activity]), FILTER(ALL(dim_user), dim_user[sk_user] = fact_activities[sk_user]))

[jrbmendes]

I've receive the message "A single value for column 'sk_user in table 'fact_activities' cannot be determined. "
This is true because there is an one to n between dim_user and fact_activities

[FreemanZ · Accepted solution]

hi @jrbmendes 

Try just plot a visual with dim_user[sk_user] column and a measure like:

Measure = COUNTROWS(RELATEDTABLE(fact_activities))

do you get what you expect?

[jrbmendes]

Ignore my previous reply. It worked. Thanks a lot.

[adudani]

@jrbmendes ,
try using ALLSELECTED if you want to accept external filters.

something like:

activities_per_user =

VAR _user_sk = dim_user[sk_user]

RETURN

COUNTX(   

               FILTER(  'fact_activities',

                             'fact_activities'[sk_user] = _user_sk,
 ALLSELECTED()
),

               fact_activities[sk_activity]

)

Please help,

Appreciate a thumbs up if this is helpful.

[jrbmendes]

Sorry. It seems to have a syntax error that I do not manage to fix