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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
letsdothis
New Member

Count unique daily contacts

Hi all,

 

I've tried to look at the forum for an answer, but without luck.

So i'm hoping someone is able to help me.

 

I have the following data example:

YEAR      TYPE          COMPANY     DATE

2018       SALES       Company1     2018-01-01

2018       SALES       Company1     2018-01-01

2018       OFFER      Company1     2018-01-01

2018       SALES       Company1     2018-01-02

2018       SALES       Company2     2018-01-02

2018       OFFER      Company2     2018-01-03

2017       SALES       Company1     2017-01-01

2017       SALES       Company2     2017-01-01

 

I want to show the number of daily unique sales.

Meaning for Company1 I want to show 2, instead of the 3 I'm getting as a result right now.

 

Thanks in advance,

 

Jim

1 ACCEPTED SOLUTION

Sorry, I replied to your post in haste. What I did was, I created a concatenated column:

 

Column = TableName[TYPE] & TableName[COMPANY] & TableName[DATE]

 

And then I created a measure using DISTINCTCOUNT:

 

Measure = Calculate(DISTINCTCOUNT(TableName[Column]),TableName[TYPE]="SALES")

 

and it worked. Please try.

View solution in original post

11 REPLIES 11
Veles
Advocate V
Advocate V

You should be able to do this without any DAX formulas at all.

 

  • Take a table visual
  • Use the date field as the values and set the option to show as Count (Distinct) and make sure you have Date selected rather than Date HeirarchyCapture.PNG

 

  • Put the Type column into the visual filters and filter on Sales only.
  • Put Company into the rows section

Thanks for your solution.

However I really need a measure for this.

Sorry, I replied to your post in haste. What I did was, I created a concatenated column:

 

Column = TableName[TYPE] & TableName[COMPANY] & TableName[DATE]

 

And then I created a measure using DISTINCTCOUNT:

 

Measure = Calculate(DISTINCTCOUNT(TableName[Column]),TableName[TYPE]="SALES")

 

and it worked. Please try.

Thank you all for your replies.

I got the solution ow, thanks!

For uniqueness you could try DISTINCTCOUNT instead of COUNT

Measure = Calculate(COUNT(Table[TYPE]),Table[TYPE]=SALES)

SALES should be enclosed in double quotes

Measure = Calculate(COUNT(Table[TYPE]),Table[TYPE]="SALES")

bizbi
Advocate I
Advocate I

Hello,

 

Are you looking for the count considering unique combination of Company Name, Date and Type? Another way to say the same: Is this what you are looking for?

 

YEAR     TYPE       COMPANY    DATECOUNT
     
2018      SALES       Company1    01-01-20182
     
2018      OFFER     Company1    01-01-20181
     
2018      SALES      Company1    02-01-20181
     
2018      SALES      Company2    02-01-20181
     
2018      OFFER     Company2    03-01-20181
     
2017      SALES      Company1    01-01-20171
     
2017      SALES      Company2    01-01-20171

Thank you for your reply.

 

Sort of, I'm looking to remove duplicates for the combination of Company Name, Date and Type.

In your table the sum of all SALES in 2018 is 4, but I only want to show 3 (as there is a duplicate on date 2018-01-01).

 

Thanks again.

Anonymous
Not applicable

Can you please clarify what you mean by daily unique sales, and how you want it broken down, by company date etc.

Sure, let me try:

 

I'm trying to count how many sales has been made to each company per year. But only 1 sales per day should be counted towards this number. This is due to the technical where each line item is added instead of only showing the one total line.

 

So from my data I have the following sales to Company1 in 2018:

YEAR      TYPE          COMPANY     DATE

2018       SALES       Company1     2018-01-01

2018       SALES       Company1     2018-01-01

2018       SALES       Company1     2018-01-02

 

But since the two first are from the same date, it should only count as one.

So my current result is showing 3, but I want to only show 2.

 

I hope it makes sense now, otherwise please reply.

 

Thanks again.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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