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.
Hi all,
I'm new to Power BI and DAX, so thanks in advance for helping.
I have a table named RUM:
date | |
crash@test.com | 2020-08-26 |
crash@test.com | 2020-08-27 |
test@dummy.com | 2020-08-27 |
test@dummy.com | 2020-08-27 |
dummy@dotcom.com | 2020-10-10 |
dummy@dotcom.com | 2020-10-10 |
kim@dot.com | 2020-01-01 |
I need to get number of distinct dates for each email address while at same time filttering some of emails.
From the table above result should be:
unique dates | |
crash@test.com | 2 |
test@dummy.com | 1 |
I have following DAX but I'm not totally sure if it gives the right kind of result.
unique dates =
CALCULATE(
DISTINCTCOUNT(RUM[date]),
FILTER(
RUM,
RUM[email] <> "dummy@dotcom.com" &&
RUM[email] <> "kim@dot.com"
)
)
I know there are multiple ways to do this and would prefer the simplest. Any help and comments will be highly appreciated.
Solved! Go to Solution.
I do not see any issue with what you have in your calculation. Another way to do it is:
unique dates =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER(RUM,RUM[email]<>"dummy@dotcom.com" && RUM[email]<>"kim@dot.com"),
"date",[date]
)
)
)
@CAPSTALKER , Try like
unique dates = CALCULATE(
DISTINCTCOUNT(RUM[date]),
FILTER(RUM, not( RUM[email] in {"dummy@dotcom.com" , "kim@dot.com" })))
I do not see any issue with what you have in your calculation. Another way to do it is:
unique dates =
COUNTROWS(
DISTINCT(
SELECTCOLUMNS(
FILTER(RUM,RUM[email]<>"dummy@dotcom.com" && RUM[email]<>"kim@dot.com"),
"date",[date]
)
)
)
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 |
---|---|
39 | |
20 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |