Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
CAPSTALKER
Helper I
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: 

emaildate
crash@test.com2020-08-26
crash@test.com2020-08-27
test@dummy.com2020-08-27
test@dummy.com2020-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:

emailunique dates
crash@test.com2
test@dummy.com1

 

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
Greg_Deckler
Community Champion
Community Champion

@CAPSTALKER -

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@CAPSTALKER , Try like

unique dates =   CALCULATE(
           DISTINCTCOUNT(RUM[date]),
           FILTER(RUM, not( RUM[email] in {"dummy@dotcom.com"  , "kim@dot.com" })))		
Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Greg_Deckler
Community Champion
Community Champion

@CAPSTALKER -

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.