Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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]
)
)
)
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |