cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Distinct values of a column with filttering by another column

Hi all,

I'm new to Power BI and DAX, so thanks in advance for helping.

I have a table named RUM:

 email 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:

 email 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.

1 ACCEPTED SOLUTION
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

@CAPSTALKER , Try like

``````unique dates =   CALCULATE(
DISTINCTCOUNT(RUM[date]),
FILTER(RUM, not( RUM[email] in {"dummy@dotcom.com"  , "kim@dot.com" })))		``````
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.