Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
hi @jrbmendes
Hope this helps 🙂
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.
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]))
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]))
2. Activities per Customer = CALCULATE(COUNT(fact_activities[sk_activity]), FILTER(ALL(dim_customer), dim_customer[sk_customer] = fact_activities[sk_customer]))
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]))
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
hi @jrbmendes
Ignore my previous reply. It worked. Thanks a lot.
@jrbmendes ,
try using ALLSELECTED if you want to accept external filters.
something like:
Appreciate a thumbs up if this is helpful.
Sorry. It seems to have a syntax error that I do not manage to fix
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |