topic Re: Sum a column based grouping by another one, but respecting user filters in DAX Commands and Tips

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

jrbmendes — Sun, 29 Jan 2023 18:25:59 GMT

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,

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

adudani — Sun, 29 Jan 2023 20:32:42 GMT

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.

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

FreemanZ — Sun, 29 Jan 2023 20:33:25 GMT

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?

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

jrbmendes — Sun, 29 Jan 2023 23:31:13 GMT

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

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

jrbmendes — Mon, 30 Jan 2023 14:00:54 GMT

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

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

Sahir_Maharaj — Mon, 30 Jan 2023 06:23:17 GMT

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

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

Sahir_Maharaj — Mon, 30 Jan 2023 06:23:55 GMT

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

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

Sahir_Maharaj — Mon, 30 Jan 2023 06:24:15 GMT

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

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

Sahir_Maharaj — Mon, 30 Jan 2023 06:24:34 GMT

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

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

Sahir_Maharaj — Mon, 30 Jan 2023 06:24:49 GMT

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.

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

Sahir_Maharaj — Mon, 30 Jan 2023 06:25:52 GMT

Hope this helps 🙂

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

jrbmendes — Mon, 30 Jan 2023 13:56:48 GMT

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