**UPDATE: PowerPivot for Excel 2010 in SQL Server 2012 and Excel 2013 support the DISTINCTCOUNT aggregation in DAX. This blog post is relevant only if you use PowerPivot for Excel 2010 in SQL Server 2008 R2.**

A PivotTable based on PowerPivot data doesn’t offer a native Distinct Count aggregation. To calculate it, you need to create a calculated measure in the PivotTable. For example, if we have a simple Sales table from AdventureWorks loaded in PowerPivot and projected into a PivotTable like the following picture.

To create a new measure, we need to right-click on the field pane the menu “Add New Measure…”

At this point we name the new measure **Distinct Customers** and write the DAX formula that calculates the number of rows returned by the DISTINCT function, which returns a list of the distinct values contained in a column (we choose EmailAddress in this example, but if we had a customer key we would have used it instead).

The DAX formula is:

**COUNTROWS( DISTINCT( Table[Column] ) )**

Where Table is the name of the PowerPivot Table containing the column and Column is the name of the column containing the values we want to count as distinct. Having defined this calculated measure, it is computed in the context defined by the PivotTable query. For example, we can look at the number of distinct customers that have bought each product, comparing that number with the number of sales for each product (which is always a number greater than or equal to the number of distinct customers).

I’d like to add a personal note. It would have been better if a shorter syntax would have been available, and much better if PivotTable would have offered the Distinct Count as a possible option in the **Summarize By** options that are available for each native measure (current available options are Sum, Count, Min, Max and Average).

Moreover, even a more direct DAX function like a DISTINCTCOUNT( [column] ) would have been easier and intuitive for “that guy”!

## Comments

## Russell A said:

Thanks very much. I'm curious, if I have blank rows in my column data, is there a way to exclude these as a distinct value?

I've tried =countrows(distinct(allnoblankrow(tbl[clm]))) but no avail :)

## Marco Russo said:

Russell,

nice comment - I wrote a blog about this:

http://sqlblog.com/blogs/marco_russo/archive/2011/03/08/difference-between-distinct-and-values-in-dax.aspx

## Serah said:

Can we use CountRows(Distinct()) for a column from aquery in Power Pivot.

Instead of selecting from table, i have columns from a query.

## Marco Russo (SQLBI) said:

Serah, what do you mean?

You can write = COUNTROWS( DISTINCT( Table[Column] ) ) but I don't understand what do you mean with "Instead of selecting from table, i have columns from a query."

Marco

## OtterMBA said:

I have a question on count(distint()) or distinctcount() - I use Denali for Excel.

I have a single table that I create a linked table into PowerPivot. On the table I have the following columns [sales staff],[customer],[year]. I have been able to do a distinctcount for 2009,2010, and 2011 just fine. ie.."=calculate(distinctcount([customer]),table1[year]=2009)"

I need to count all of the customers that we did business with in 2009 and 2010 but not 2011, 2011 only, 2010 only, etc. and every iteration of the years. Once I figure out the formula I know I can manipulate for the others.

Curretly I build a pivot table by salesperson,and customer by distinctcount by year. Then use an if formula in an empty cell like if(and(c1=1,d1=1,e1=0),1,0) to figure out customers for 2009 and 2010 only. Then I total all the 1s for each salesperson. Is there a way to create a measure in powerpivot to do this?

## Marco Russo (SQLBI) said:

Do you know the possible combination in advance, or do you want to select a list of "DO" and a list of "DO NOT" and having a dynamica calculation based on that?

In the latter, take a look at this scenario in this case, because is very similar to the requirement.

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/05/19/powerpivot-basket-analysis-and-the-hidden-many-to-many.aspx

## Johannes said:

Is there a difference between countrows(distinct()) and distinctcount() ?

## Marco Russo (SQLBI) said:

Johannes, no differences - actually DISTINCTCOUNT is more clear from a semantic point of view, but internally COUNTROWS(DISTINCT()) is computed instead, so they are identical from a computational point of view.

## Johannes said:

Hey Marco, forgot to check back until now - thanks for clarifying that!

## Pathik Bhatt said:

Its been sometime since, I have been trying to get my head around this. I have imported information on close to 1.5 million customer orders in a powerpivot table. Customer orders generate different delivery packets. Sometimes for the same customer orders there are several packets generated. So the data looks like:

Order,Package

e1,p1

e1,p1

e1,p2

e1,p2

e1,p3

e2,p5

e2,p6 etc..

What I want to do is calculate in adjacent field a count of all unique packages that the customer order has generated. In the above example that would be:

Order,Package

e1,p1,3

e1,p1,3

e1,p2,3

e1,p2,3

e1,p3,3

e2,p5,x …

I just learnt about powerpivot and am not able to understand how this could be achieved. I need this field for calculation purpose. Many Thanks!

## Marco Russo (SQLBI) said:

Sorry for big delay in answering, I missed your comment!

Assuming the table you used as an example is called Orders, the solution is the following one (in PowerPivot 2012)

= CALCULATE(DISTINCTCOUNT(Orders[Package]),ALL(Orders[Package]))

In PowerPivot v1 you can obtain the same result by writing:

= CALCULATE(COUNTROWS(DISTINCT(Orders[Package])),ALL(Orders[Package]))

## HK said:

Thanks for posting this. It saved me a lot of time on a project.

## Aviad said:

I have that tableuser number med

1 22

1 22

1 22

2 33

2 33

2 33

1 232

3 44

3 42

3 42

4 50

4 50

4 50

5 98

6 464

7 321

12 9

8 8

15 51

16 52

32 97

3 41

what formula can count number of distinct meds per user ? (i.e user 1 = 2 meds)

Thanks!

Aviad

## Marco Russo (SQLBI) said:

You can use this formula in a calculated column:

=CALCULATE ( DISTINCTCOUNT ( tableuser[med] ), ALL ( tableuser[med] ) )

Or this in a measure:

MC := DISTINCTCOUNT ( tableuser[med] )

## Calderon said:

Very useful. Thank you for the formula.

## Kara said:

Hi I have table with following fields. I have to find average selling price by SKU and also count of Distinct SKU by customer and operating unit. Please help.

operating_unit customer SKU Selling price

USA ABCD P1 100

USA PQR P1 80

USA LMN P1 20

USA ABCD P2 30

UK XYZ P1 50

UK XYZ P2 100

## Marco Russo (SQLBI) said:

Kara,

the average selling price can be obtained by using

AVERAGE ( Table[Selling Price )

putting the SKU on the rows of the PivotTable.

The same is true for getting the distinct count of SKU, create a measure with:

DISTINCTCOUNT ( Table[SKU] )

and put customer and operating unit on the rows of the PivotTable.

Marco

## smita said:

unable to use distinctcount option.

## Marco Russo (SQLBI) said:

smita, do you have the latest version of PowerPivot for Excel? The first version didn't have DISTINCTCOUNT function in DAX.

## Jamal said:

I have a table of customers and their related invoices. I want to get when the first invoice was issued (month & year).

## Marco Russo (SQLBI) said:

Jamal,

the easiest way is creating a calculated column in Customers table with the formula:

= CALCULATE ( MIN ( Invoices[Date] ) )

So that you get the minimum date of the invoices of each customer.

Marco

## MrChuckles said:

Thank you! I found your site as the top hit on Google and you really helped me. Your explanation of counting distinct values was something I had been trying to teach myself through trial and error to no avail. Your example made it a two minute process. I appreciate you dude!

## Chris Gilbert said:

I have a powerpivot table with dates on rows showing the distinctcount of orders for the given date. I would like to display on each row the average and max number of orders over all of the dates shown, so that I can display an average and max line on the pivot chart.

The problem I have is how to get the max of the distinctcount() or the maxx( ) of the in-memory distinct() table values.

Can you hel me?

## Marco Russo (SQLBI) said:

Chris,

you should use CALCULATE with the same measure over a different period.

For example, if you have:

[Orders] := DISTINCTCOUNT ( Orders[OrderID] )

then you can write:

[Monthly Orders] :=

IF (

HASONEVALUE ( Calendar[Month] ),

CALCULATE (

[Orders],

PARALLELPERIOD ( Calendar[Date], 0, MONTH )

)

)

## Chris Gilbert said:

Thanks for the reply.

I found that this [Monthly Order] measure just returns the total of all the DistinctCount, not the max of all those entries.

Any ideas?

Chris

## Marco Russo (SQLBI) said:

Sorry for the misunderstanding.

Can you show an example (with numbers) of what you want on each row? There are at least 3-4 possible interpretations of your request.

Marco

## Chris Gilbert said:

All of this just to be able to place an AvgOrders and MaxOrders dashed line across a pivot chart!

Row Labels NumOrders Average Max

8/1/2002 9 9.1 13

8/2/2002 8 9.1 13

8/3/2002 6 9.1 13

8/4/2002 9 9.1 13

8/5/2002 7 9.1 13

8/6/2002 12 9.1 13

8/7/2002 11 9.1 13

8/8/2002 8 9.1 13

8/9/2002 8 9.1 13

8/10/2002 4 9.1 13

8/11/2002 5 9.1 13

8/12/2002 9 9.1 13

8/13/2002 12 9.1 13

8/14/2002 10 9.1 13

8/15/2002 12 9.1 13

8/16/2002 5 9.1 13

8/17/2002 10 9.1 13

8/18/2002 8 9.1 13

8/19/2002 11 9.1 13

8/20/2002 12 9.1 13

8/21/2002 10 9.1 13

8/22/2002 10 9.1 13

8/23/2002 5 9.1 13

8/24/2002 11 9.1 13

8/25/2002 11 9.1 13

8/26/2002 13 9.1 13

8/27/2002 11 9.1 13

8/28/2002 8 9.1 13

8/29/2002 6 9.1 13

8/30/2002 8 9.1 13

8/31/2002 12 9.1 13

Grand Total 281

## Marco Russo (SQLBI) said:

Ok so you might use:

[Avg Orders] := AVERAGEX ( Calendar[Date], [Orders] )

[Max Orders] := MAXX ( Calendar[Date], [Orders] )

[Monthly Avg Orders] :=

CALCULATE (

[Avg Orders],

PARALLELPERIOD ( Calendar[Date], 0, MONTH )

)

[Monthly Max Orders] :=

CALCULATE (

[Max Orders],

PARALLELPERIOD ( Calendar[Date], 0, MONTH )

)

## Chris Gilbert said:

Both the [Avg Orders] and [Max Orders] measures return an error...

The value for column 'Date' in table 'Calendar' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

## Chris Gilbert said:

Got it! The reference to "Calendar[Date]" in both [Avg Orders] and [Max Orders] just needed to the the table name "Calendar".

Thanks for your help. I hadn't even heard of the PARALLELPERIOD() function, so I've got some investigation to do.

Chris

## Chris Gilbert said:

That works for when I only have one month of dates on the PivotTable Rows.

If I expand the number of dates to a full year, then I get an Average that changes at every transition to the next month.

If I change the [Monthly Avg Orders] to use YEAR in the PARALLELPERIOD function, then I get the correct average for the year, but switching back (via slicer) to just one month of data still gives me the yearly average for any month that I select.

I tried changing MONTH to DAY, which the context sensitive help for the PARALLELPERIOD states is a valid parameter, but the {Check Formula} feature rejects DAY as invalid.

I'll keep after it.

## Marco Russo (SQLBI) said:

Chris,

you might want to implement some IF statement using HASONEVALUE in order to check whether you have selections at year or month level.

I am not sure to be able to help more here, not having the full data model and all the details of your specific issue.

I hope you understand - if you want direct consultancy you can see our offer here: http://www.sqlbi.com/consulting

Marco

## Chris Gilbert said:

Thanks, Marco

I understand, and appreciate all the help. And, hopefully, others can also benefit from this discussion, as well.

I just found it "interesting" that it was so "non-intuitive" on how to get an AVG or MAX of the values resulting from the DISTINCTCOUNT function... which was one of the most obvious things I've ever had to explain to new users of PowerPivot.

## Marco Russo (SQLBI) said:

Chris,

there are technical explanations (PivotTable in Excel use MDX, internal Language of PowerPivot is DAX, ...) but I agree with you that the final result is that the mix makes some common operations not enough intuitive. A "visual" max/avg on anything you see in the cells of a PivotTable would make life easier to some users.

Marco

## Rene Ortiz said:

I have a table with the following fields and need to find a count of distinct for IssueType1, IssueType2, and IssueType3. I'm using PowerPivot w/Excel 2010

TeamMemberDept IssueType1 IssueType2 IssueType3

Dept1 Due Date Change Improper Call Conduct Late Fees

Dept1 Payment Processing Late Fees Payment Channels

Dept3 Service Member Issue Deferment Payment Channels

Dept3 Excessive Calls Improper Call Conduct Late Fees

Dept4 Due Date Change Deferment Simple Interest

Dept5 Credit Bureau Reporting Improper Call Conduct Improper Call Conduct

Dept2 Credit Bureau Reporting Improper Call Conduct Deferment

Dept6 Excessive Calls Reference/Nearby Calls Billing Statement

Dept7 Excessive Calls Improper Call Conduct Payment Processing

Dept1 Excessive Calls Payment Arrangement Improper Call Conduct

Dept2 Verification / Probing Question Process Deferment Improper Call Conduct

## Rene Ortiz said:

One more thing to add to my question above...some fields contain blanks, so I would like to avoid including any blank values!

Thanks

## Marco Russo (SQLBI) said:

Rene,

you can obtain the result with:

= CALCULATE (

DISTINCTCOUNT(Table[IssueType1]),

NOT(ISBLANK(Table[IssueType1]))

)

Marco

## Rene Ortiz said:

Thanks Marco for the formula, but I get "This is not a valid DAX expression

Here is the actual formula I'm using: =CALCULATE(DISTINCTCOUNT('Table_ComplaintsProd'[IssueType1]),NOT(ISBLANK('Table_ComplaintsProd'[IssueType1])))

## Rene Ortiz said:

I also want to be able to get one count for distinct values from IssueType1, IssueType2 and IssueType3, if that is possible?

Thanks for your time!

## Marco Russo (SQLBI) said:

Create a calculated column that has something like

= Table[IssueType1) & "|" & Table[IssueType2) & "|" Table[IssueType3)

and do the DISTINCTCOUNT on such a column.

Another technique is doing that at Query time, but it might affect performance and memory requirement in a very bad way.

Marco

## Richi said:

Hi Marco,

How can one calculate a sum based on distinct fields? Say for example the field in this case is "ID":

ID Amt

1 5.00

2 10.00

2 10.00

3 15.00

4 20.00

SUM AMT calculate field should equal 10.00 for ID 2.

Please advise.. Thanks..

## Marco Russo (SQLBI) said:

Use this:

SUMX (

SUMMARIZE (

Table,

Table[ID],

Table[Amt]

),

Table[Amt]

)

## Richi said:

Thanks Marco! Works for me!

## Lydia said:

Hi Marco,

I'm trying to get a calculated column that has calculates the average number of days in stock per SKU...

I tried using a similar approach with using

=AVERAGEX(SUMMARIZE(Sold,Sold[IC unique],Sold[Time in Stock]),Sold[Time in Stock])

This is just giving me a total average for all SKUs. Is it possible to do it per SKU?

Thanks for your time!

## Marco Russo (SQLBI) said:

If the SUK is a column of the same table, you could use this:

=

AVERAGEX (

SUMMARIZE (

FILTER ( Sold, Sold[SKU] = EARLIER ( Sold[SKU] ) ),

Sold[IC unique],

Sold[Time in Stock]

),

Sold[Time in Stock]

)

## Amber said:

hi marco,

I am trying to find a net sales over time per sku based on the order of the invoice numbers. The negative charges are credits. Here is how my table is set up:

invoice_no amount charged SKU

1 100 A

2 -100 A

3 150 B

4 -100 B

5 200 A

6 0 A

I want to have the net amount charged for sku A to be 200 and sku B to be 50. I have tried

=sumx(filter('invoices', 'invoices'[sku]=EARLIER('invoices'[sku]) && 'invoices'[invoice_no]>=EARLIER('invoices'[invoice_no])),[Amount Charged])

but this is giving me the wrong answer. Any suggestions?

## Marco Russo (SQLBI) said:

Amber, can you describe me why A should be 200? I see 100, -100, 200, 0, which totals should be 300 and not 200.

Marco

## Amber said:

Marco,

A should equal 200. 100 + (-100) + 200 + 0 = 200.

Amber

## Marco Russo (SQLBI) said:

Amber, you are right, sorry for my confusion. So, why this is not good?

SUM ( Invoices[Amount Charged] )

## Amber said:

I need it to be over time. For example I want it to look like...

invoice_no amount charged SKU Net Over Time

1 100 A 100

2 -100 A 0

3 150 B 150

4 -100 B 50

5 200 A 200

6 0 A 200

I've tried changing my formula a little bit, but it still isn't performing correctly.

=sumx(summarize(filter('invoices', 'invoices'[sku]=EARLIER('invoices'[sku]) && 'invoices'[invoice_no]<=EARLIER('invoices'[invoice_no]), 'invoices'[SKU], [Amount Charged]),[Amount Charged])

## Marco Russo (SQLBI) said:

It seems you want to create a calculated column - in that case this should be the formula to use:

=

SUMX (

FILTER (

'invoices',

'invoices'[sku] = EARLIER ( 'invoices'[sku] )

&& 'invoices'[invoice_no] <= EARLIER ( 'invoices'[invoice_no] )

),

'invoices'[Amount Charged]

)

## Amber said:

Thank you!

## San kar said:

Marco, New to Power Pivot. Want to calculate the ranking of the code

I have a 2 tables a table1

name, code

A, D1

B, D2

C, D1

E, D3

F, D2

G, D3

H, D3

table2 where I want to calculate row counts from table1 and rank the codes based on the count for each code as shown here

code, Count, rank

D1, 2, 2

D2, 2, 2

D3, 3, 1

Thanks

## Marco Russo (SQLBI) said:

Assuming you have a relationship between table1 and table2, you can use:

Count = CALCULATE ( COUNTROWS ( table1 ) )

Rank = RANKX ( ALL ( table2 ), table2[Count] )

## San Kar said:

Thank you.

## Sagar said:

Hi,

For excluding blanks from Calculation I tried these two DAX statements but these are working very slow in reports.,

:= CALCULATE (

DISTINCTCOUNT(Table[IssueType1]),

NOT(ISBLANK(Table[IssueType1]))

)

:= CALCULATE (

DISTINCTCOUNT(Table[IssueType1]),

Table[IssueType1] <> Blank())

)

And tried this ,

:= CALCULATE (

DISTINCTCOUNT(Table[IssueType1]),

ALLNOBLANKROW( Table[IssueType1]))

)

But this is fast but not excluding Blanks.

Do we have any better DAX statement which will vomit Blanks and give good performance as well?

Thanks for your help.

## Marco Russo (SQLBI) said:

How many unique values do you have in IssueType1 column?

If there are thousands or more, you should create a calculated column "IsIssueType1Blank" with the formula ISBLANK(Table[IssueType1]) and then transform the measure in:

:= CALCULATE (

DISTINCTCOUNT(Table[IssueType1]),

NOT(Table[IsIssueType1Blank])

)

## Sagar said:

Yes, it has thousands of unique values.

And using this method it's working good.

Thank you...

## Pete said:

Marco, am trying to count customers in regions with thresholds. Example is counting all customers(2) with sales in both the US and Canada with total sales >= 8,000.

CustNo Region Sales

111 US 8,000

222 Canada 2,000

333 EMEA 1,500

111 Canada 7,000

222 India 1,000

444 Canada 9,000

222 US 6,000

Tried distinctcount and calculate but could only filter by either US or Canada instead of both; and also didn't know how to sum sales at the same time to check the threshold.

## Marco Russo (SQLBI) said:

Pete, in this case you should count the Customer resulting from a filter obtained with a CALCULATE the filter both countries:

COUNTROWS ( FILTER ( Customers, CALCULATE ( [Sales], Territory[Country] = "USA" || Territory[Country] = "Canada" ) > 8000 ) )

## Pete said:

Thanks, got the concept. Is there a way to count only a given customer with combined sales over 8K in US & Canada? Exclude cust 444 in the example. My results are now including more than cust 111 and 222.

## Marco Russo (SQLBI) said:

It is just more complex the filter predicate:

COUNTROWS ( FILTER ( Customers,

CALCULATE ( [Sales], Territory[Country] = "USA" || Territory[Country] = "Canada" ) > 8000

&&

CALCULATE ( [Sales], Territory[Country] = "USA" ) > 0

&&

CALCULATE ( [Sales], Territory[Country] = "Canada" ) > 0

) )

## Pete said:

Added the filters but am now getting zeros for the count. Appreciate any advice.

## Marco Russo (SQLBI) said:

It should work for the example you posted. Check the correct syntax in your version.

## Marco Russo (SQLBI) said:

It should work for the example you posted. Check the correct syntax in your version.

## Pete said:

Thank you! Works great. It was my issue as I just needed to add the column to the countrows filter: COUNTROWS (FILTER(VALUES(Customers[CustNo]). Your advice was the most useful anywhere.

## Nahmja said:

My Actual data Is

Order Date TIME Denomination Quantity MERCHANT ID Addres Amount Gross Amount Net Discount

2206782 01/10/2014 20:22:30 P_HAYYAK_5 20 201010100500200 607-GHALA 100 95.8 4.2%

4206782 01/10/2014 20:22:30 P_HAYYAK_5 20 201010100500200 607-GHALA 100 95.8 4.2%

4206783 2/10/2014 20:22:30 P_HAYYAK_5 20 201010100500200 607-GHALA 100 95.8 4.2%

4206784 2/10/2014 20:22:30 P_HAYYAK_5 20 201010100500200 607-GHALA 100 95.8 4.2%

4206785 01/10/2014 20:22:30 P_HAYYAK_5 20 201010100500200 607-GHALA 100 95.8 4.2%

4206786 01/10/2014 20:22:30 P_HAYYAK_5 20 201010100500200 607-GHALA 100 95.8 4.2%

2206294 01/10/2014 17:53:31 P_HAYYAK_5 10 201010100500201 607-GHALA 50 47.9 4.2%

2206350 01/10/2014 18:22:20 P_HAYYAK_5 20 201010100500202 607-GHALA 100 95.8 4.2%

2206351 2/10/2014 18:22:20 P_HAYYAK_5 20 201010100500202 607-GHALA 100 95.8 4.2%

2206399 01/10/2014 18:37:19 P_HAYYAK_5 20 201010100500203 607-GHALA 100 95.8 4.2%

2206430 01/10/2014 18:46:45 P_HAYYAK_5 14 201010100500204 607-GHALA 70 67.06 4.2%

2206506 01/10/2014 18:58:05 P_HAYYAK_5 20 201010100500205 607-GHALA 100 95.8 4.2%

2206623 01/10/2014 19:27:14 P_HAYYAK_5 20 201010100500206 607-GHALA 100 95.8 4.2%

2206333 01/10/2014 17:53:31 P_HAYYAK_5 10 201010100500201 607-GHALA 50 47.9 4.2%

2206337 2/10/2014 17:53:31 P_HAYYAK_5 10 201010100500201 607-GHALA 50 47.9 4.2%

Required result as below (using Powerpivot)

MERCHANT ID 01/10/2014 2/10/2014 No Of Orders Average Nov visit Grand Total

201010100500200 400 200 6 300 2 600

201010100500201 100 50 3 75 2 150

201010100500202 100 100 2 100 2 200

201010100500203 100 1 100 1 100

201010100500204 70 1 70 1 70

201010100500205 100 1 100 1 100

201010100500206 100 1 100 1 100

## Sagar said:

Hi, Want to filter Customers who have at least one active subscription.There are cases where single customer can have one active and one inactive subscriptions, I want to flag these type of customers also to "true".and customer with only inactive subs to "false",Is there any way to achieve this with DAX?

## Marco Russo (SQLBI) said:

Sagar,

you can compare two expressions, one filtering all the subscriptions and one filtering only the inactive ones - if all of them are inactive it is false, otherwise is true.

Marco

## Stacy said:

I am trying to get a count of numbers excluding zeros without success, anyone to help? I keep getting a count with zeros included.

Thanks.

## Marco Russo (SQLBI) said:

Stacy, use this:

CALCULATE ( COUNTROWS ( FILTER ( VALUES ( table[column] ), table[column] <> 0 ) ) )

## Stacy said:

Thanks Marco.

The above formula works fine and eliminates the zeros counts, however the duplicate values in columns are counted only once and not twice. i.e

in 1988, Australia had 2 occurrences of 1, I need to get a count of 2 and not 1.

Also, the total count should be 9 and not 1 as the one I'm getting. The first V count gives 9 as I required but that formula is counting zeros in other rows.

Thanks in advance.

Country V Count V Count(i)

Alaska 1 1

1986 1 1

Australia 9 1

1952 1 1

1963 1 1

1965 1 1

1966 1 1

1978 1 1

1980 1 1

1988 2 1

1993 1 1

Austria 1

1971 1

Belgium 1

1989 1

Brazil 5 1

1957 2 1

1958 1

1977 1 1

1996 1 1

## Marco Russo (SQLBI) said:

So use this:

CALCULATE ( COUNTROWS ( FILTER ( table, table[column] <> 0 ) ) )

## Stacy said:

Thanks Marco, it works!

Grateful for this blog, I'm learning so much.

## Sameer said:

Hi:

I am trying to do this with DAX and unable to get the correct distinct count...

AcountID OrderDate Product SalesAmount DiscountAmount

1023 Nov-14 Product001 $- $5.00

1045 Nov-14 Product001 $30.00 $10.00

1023 Nov-14 Product002 $1.00 $-

1089 Nov-14 Product001 $- $5.00

2045 Nov-14 Product001 $50.00 $25.00

1045 Nov-14 Product001 $(30.00) $-

Q1. How do I count Distinct Customers where Total Sales Amount was $0 (Answer is 2, 1045, 1089) - see here I want to ignore 1023 because the total Sales amount for that account is not $0.

I tried to create a measure: TotalCustomers:=CALCULATE(DISTINCTCOUNT(Table1[AcountID]), 'Table1')

And then added this: ZeroCustomers:=CALCULATE([Total Customers], FILTER('Table1', [Sum of SalesAmount]<>0))

But account 1023 gets counted and I get '3' for ZeroCutomers

Q2. How do I add up DiscountAmount for Customers where Total Sales Amount was $0 (Answer $15)

This is an extension to Q1 - once I have figure out Q1 - I can probably figure this one out.

My other choice is to create a new table already summarized by AccountID - but then I wont be able to use slicer since I still want to slice by lets say Product and other Dimensions.

Any help will be appreciated!

Thanks!

## Sameer said:

I may have figured it out:

Q1

ZeroCustomers:=COUNTROWS (

FILTER (

ADDCOLUMNS (

VALUES ( Table1[AccountID] ),

"TotalSalesAmount", CALCULATE ( SUM ( [SalesAmount] ) )

),

[TotalSalesAmount] = 0

)

)

Q2

ZeroCustomerDiscount:=CALCULATE(SUM([DiscountAmount]),

FILTER (

ADDCOLUMNS (

VALUES ( Table1[AccountID] ),

"TotalSalesAmount", CALCULATE ( SUM ( [SalesAmount] ) )

),

[TotalSalesAmount] = 0

)

)

If there is a better way then please let me know.. I have also posted the question here: http://stackoverflow.com/questions/29423452/conditional-distinct-count-based-on-a-measure-in-dax

Hope this helps other!

## Drew said:

Hi Marco,

I want to know the number of visits my clinic received within a certain time period.

The columns I have are: visit type (Text field), date (ie 1/1/2015).

Is there a way to show how many types of visits we received in a given time period? Last month, YTD and monthly average?

Thank you

## Drew said:

How can I search multiple months?

=CALCULATE(COUNTROWS('DATA 1'),SEARCH("Mar2015",'DATA 1'[MonYear],1,0)>=1)

In this example I am gathering all Mar2015. I want to count more months like this:

=CALCULATE(COUNTROWS('DATA 1'),SEARCH("Feb2015","Mar2015","Apr2015",'DATA 1'[MonYear],1,0)>=1)

Will this work or is there another way?

## Marco Russo (SQLBI) said:

@Sameer

For Q1 your formula is ok

So for Q2 you might also use this (but I think you don't have side effects using your technique, which should be good as well):

CALCULATE (

SUM ( t[DiscountAmount] ),

SUMMARIZE (

FILTER (

ADDCOLUMNS ( VALUES ( t[AccountID] ), "s", CALCULATE ( SUM ( t[SalesAmount] ) ) ),

[s] = 0 ),

t[AccountID] )

)

I hope it helps.

## Marco Russo (SQLBI) said:

@Drew - there are many time intelligence functions in DAX, and you can find alternative versions here: http://www.daxpatterns.com/time-patterns/

Regarding your second questions, you should use an OR function (or the || operator) using one SEARCH for each month - but I would suggest you using a column MonthNumber instead, it's easier and faster to test a number then to search a word in a string.

## Robin A said:

Hi

I have read many of your blogs and am impressed with your solutions. I am about to teach me PowerPivot. My question feels difficult and I have not been able to find any solution. in the accompanying excempel I have an individual having some activities. all the rows are duplicates because the charge period is different from the activity period. The lines can I not affect. I'm looking to be able to get the number of days between "active start" and "active end", without duplicates. I would like the same with the period "cost start" and "cost end". I also want to be able to get the cost per day. All this should be used so that it can to get a table that can be allocated per year, or month.

PR ID KEY Active start Active end cost start cost end cost period

318 3366 2013-10-07 2014-01-06 2013-10-07 2013-10-31 21 590

318 3366 2013-10-07 2014-01-06 2013-11-01 2013-11-30 26 670

318 3366 2013-10-07 2014-01-06 2013-12-01 2013-12-31 22 860

318 3366 2013-10-07 2014-01-06 2014-01-07 2014-01-31 27 500

318 3366 2013-10-07 2014-01-06 2014-02-01 2014-02-28 25 400

318 3366 2013-10-07 2014-01-06 2014-03-01 2014-03-05 3 350

318 3366 2014-01-07 2014-03-05 2013-10-07 2013-10-31 21 590

318 3366 2014-01-07 2014-03-05 2013-11-01 2013-11-30 26 670

318 3366 2014-01-07 2014-03-05 2013-12-01 2013-12-31 22 860

318 3366 2014-01-07 2014-03-05 2014-01-07 2014-01-31 27 500

318 3366 2014-01-07 2014-03-05 2014-02-01 2014-02-28 25 400

318 3366 2014-01-07 2014-03-05 2014-03-01 2014-03-05 3 350

Many thanks in advance.

## Marco Russo (SQLBI) said:

Hi Robin, I'm not sure about your requirements. Probably you want to iterate over a DATE table to check how many days are within each range.

## Robin A said:

Hi

Thank you for taking your time looking at my question. As you can see in the examples there are the same data on multiple lines. With some date Difference. This means that everything will be twice or more, depending on the date differences. The basic material, can I not affect. The formula I need shall be able to count the number of days between the date periods These shall be unique, no matter how many double lines there are. The formula shall not to include any lines that look the same. Based on this, I'll also be able to obtain the cost per day. I have the total.

ex:

Active start / end active = number of days

Start cost / end cost = number of days, it will also provide cost-per-day, I have a grand total

Only unique lines counted

I hope I could clarify my question a little more.

Thanks

## Marco Russo (SQLBI) said:

You still do not clarify what to do when you have two identical rows for a product. Your formula is easy to do on a row-by-row basis, but I don't understand what do yo want to do with rows with identical values (and identical in which columns? In your example the first two columns are always the same).

## Robin A said:

Hi

Thank you for your patience. I'll try to explain again. I tryd to color-marke differences in periods of color, but its not possible to paste it here.

column 1=ID

column 2=actviestart

Column 3=activeend

Thees periods are not allways same date. As you can se I have got two periodds. first period 2013-10-07 -- 2014-01-06 and second period 2014-01-07 -- 2014-03-05 and there i several of them. I want to Count them distinct. If I Count them row by row the result is going to be 900 Days.in real they are only 150.

Column 4=coststart

Column 5=costend

Column 6=total cost for the period

Thees are the period for the cost and total cost. Thees can also be different. as you can se are they also dubbel. I want them also distinct.It is the sam here. If I Count every row result will be 288 Days instead of 144

Wanted result in powerpivot

PR ID Activestart Activeend coststart costend total periodcost Activedays costdays cost/day

318264 2013-10-07 2014-01-06 2013-10-07 2013-10-31 21590,00 92 25 863,60

318264 2013-10-07 2014-01-06 2013-11-01 2013-11-30 26670,00 0 30 889,00

318264 2013-10-07 2014-01-06 2013-12-01 2013-12-31 22860,00 0 31 737,42

318264 2013-10-07 2014-01-06 2014-01-07 2014-01-31 27500,00 0 25 1100,00

318264 2013-10-07 2014-01-06 2014-02-01 2014-02-28 25400,00 0 28 907,14

318264 2013-10-07 2014-01-06 2014-03-01 2014-03-05 3350,00 0 5 670,00

318264 2014-01-07 2014-03-05 2013-10-07 2013-10-31 21590,00 58 0 0,00

318264 2014-01-07 2014-03-05 2013-11-01 2013-11-30 26670,00 0 0 0,00

318264 2014-01-07 2014-03-05 2013-12-01 2013-12-31 22860,00 0 0 0,00

318264 2014-01-07 2014-03-05 2014-01-07 2014-01-31 27500,00 0 0 0,00

318264 2014-01-07 2014-03-05 2014-02-01 2014-02-28 25400,00 0 0 0,00

318264 2014-01-07 2014-03-05 2014-03-01 2014-03-05 3350,00 0 0 0,00

Thees are the results I got with my nolege

Row Aktivedays Costdays

2013 516 172

okt 150 50

nov 180 60

dec 186 62

2014 384 116

jan 186 50

feb 168 56

mar 30 10

Wanted result for presentation

Row Aktivedays Costdays cost

2013 86 86

okt 25 25

nov 30 30

dec 31 31

2014 64 58

jan 31 25

feb 28 28

mar 5 5

THANK YOU

## Marco Russo (SQLBI) said:

From what you write, I understand that you *do not have* duplicated rows and you simply want to calculate certain information row by row, and *then* you want to aggregate data.

If this is correct, you should do the calculation row-by-row using calculated column, but if you want to aggregate by month and you have overlapping rows (e.g. a row cover more than one calendar month) then you have to either split the row in ETL generating multiple rows, or create a measure that iterates row that intersect possible periods (without a relationship between fact table and date table) and perform the calculation.

If you need examples, take a look at www.daxpatterns.com - there is not a pattern identical to your needs, but you might get some ideas from Parameter, Segmentation, and Budgeting patterns.

## Robin A said:

Hi

It is correct. I dont need to have them row by row. I whant to aggregate Days in period by month.

ex: distinct (PR ID) agregate Days between activestart and Activeend by month.

I shall look for exampels. I would be more than grateful if you would help me with a suggested formula

Thank you

## Marco Russo (SQLBI) said:

Robin,

there are many details involved in writing a formula - if you want a specific consultancy visit www.sqlbi.com/consulting, otherwise please find a repro on AdventureWorks (or similar public databases) so that I can spend time to a solution that can be easy to use also for other Readers.

Thanks!

Marco

## Gaurav Kumar said:

operating_unit customer SKU Selling price

USA ABCD P1 100

USA PQR P1 80

USA LMN P1 20

USA ABCD P2 30

UK XYZ P1 50

UK XYZ P2 100

How to find no of SKU for each Operating Unit ?

to get output as :-

p1 p2

USA 3 1

UK 1 1

and how to divide the SKU and Operating unit in two different Table.

Thanks in advance.

## Gaurav Kumar said:

I solved the problem. Thanks.

## Marco Russo (SQLBI) said:

Gaurav, thanks for the update!

## Sagar said:

Hi ,

Can we get dynamic percentage(in a new calculated column) of two grand totals in excel pivot table?

Thanks ....!

## Big Stew said:

hey have you ever counted unique employees that had all 7 critirea's...example 5 sales reps....40 sales items....and if i wanted to know how many sales reps sold atleast 1 of 7 key sales items?..however the sales items are in the same column

## ross said:

Hi:

How do I do distinct count for the below in dax?

ID Status

1 Terminated

1 Terminated

2 Active

3 Terminated

4 Terminated

4 Terminated

5 Active

I want to find out total terminated (distinct not instance).

Thank you.

## Big Stew said:

to ross...Calculate(distinctcount([id column]),'table name'[status column]="terminated")

## ross said:

Thank you very much.

Is it possible to add 2 measures from different tables? thanks again.

## Big stew said said:

to ross: yes

## ross said:

Thank you again.

ID Date Pay

1 3/1 1000

1 3/15 2000

2 4/1 1000

2 4/15 2000

1 3/1 1000

1 3/15 2000

2 3/1 100

2 3/15 200

How do I find total pay for each id?

This is what I did in 2 steps,

Item = max(table[Pay]) then

Total pay = Sumx(DISTINCT(table[Id]),table[Item])

But it give results like,

for ID 1 = 1000

It should be 3000 instead.

Can any one help how to resolve it. I am not sure how to nest the distinct function. Thanks.

## ross said:

I came up with a workaround for the above problem,

combId =ID&Date

Item = max(table[Pay])

Total pay = Sumx(DISTINCT(table[combId]),table[Item])

It worked, atleast I am getting the results I want. If there is any other clever Dax pattern please let me know.

I bought "The definitive Guide to Dax and Dax Patterns by Marco.R and Alberto Ferrari. It is worth every cents. Just my 2 cents. Thanks.

## Big Stew said:

you could just create a measure called "Total Pay" and its Sum(column name)...and if you create a pivot table and put id's in row...."Total Pay measure in field...it will give you what you need

## Big Stew said:

has Marco Russo (SQLBI) given up? If not, can you take a look at my dilemma

## Big Stew said:

I have tried:

CALCULATE(DISTINCTCOUNT(CED[Sales ID]),FILTER(CED,CED[Sales Items]="DMX" && CED[Sales ITems]="Buster Rhymes")

## Big Stew said:

but it's 5 additional items...that was just an example

## ross said:

Thank you. In power pivot we can do lot of manipulations. I am using power Bi and not much room to use pivot tables etc.

## Big Stew said:

Yes...With power bi...the above works...If you just want a sum of pay by id and date...a calculated measure of "Pay" will allow you that flexibility...

## ross said:

I need help to write dax for the below scenario.

Id Dept pay

1 M 10

1 M 15

2 E 10

2 E 10

3 M 20

3 M 25

4 E 10

Output should be for dept M, Avg Pay = 35

I need to find average pay for each dept. Please give your suggestions. Thank you.

## Big Stew said:

create a measure called Avg Pay = Average([pay])...then create a pivot table with Dept. in row, and avg pay in value

## Big Stew said:

create a measure called Avg Pay = Average([pay])...then create a pivot table with Dept. in row, and avg pay in value

## Big Stew said:

create a measure called Avg Pay = Average([pay])...then create a pivot table with Dept. in row, and avg pay in value

## Big Stew said:

## Big Stew said:

## ross said:

Without pivot table is i possible. I am working in power bi. Thank you.

## Big Stew said:

yes its possible even in power bi...you may have to = calculate(average([pay]),'model name[dept]="m"))

## ross said:

Thank you. How is ID taken into account here? Still not able to get the answer right. Thanks.

## Marco Russo (SQLBI) said:

@Sagar: you can create a measure (or calculated field - not calculated column) to create a percentage between two measures, but I suggest you to use explicit measures and not implicit ones.

@Big Stew: you can obtain the number you want through CALCULATE ( COUNTROWS ( SalesRep ), Sales ) - take a look at the many-to-many paper here: http://www.sqlbi.com/articles/many2many/

@ross: you probably want to do something like the following (replace the first AVERAGEX with SUMX in case you want to sum different departments in the grand total):

AVERAGEX (

VALUES ( table[Dept] ),

CALCULATE (

AVERAGEX (

VALUES ( table[Id] ),

CALCULATE ( SUM ( table[pay] ) )

)

)

)

## ross said:

Thank you very much. Appreciate it.

## meshack said:

i have data arraged as follows:

customer id visit id

1001 1

1002 3

1005 2

1004 4

1001 6

1005 11

1002 5

1005 8

1001 16

for each customer i want to list how many times he/she was visted by cunting visit ids, example customer 1001 was visited 3 times.

## Marco Russo (SQLBI) said:

You can use DISTINCTCOUNT ( tablename[visit_id] )

## meshack said:

thanks

## Big Stew said:

Hey Can you create a ytd measure...that can be controlled by a slicer/filter..example jan i had 3 sales, feb i had 2 sales, Mar i had 2 sales...total of 7 by mar...however if i select feb...i want total of 5 (jan and feb combined)...and when i select Mar it combines Jan,feb,mar to equal 7

## Marco Russo (SQLBI) said:

You have to implement a CALCULATE ( [original measure], DATESYTD ( 'Date'[Date] ) )

It's standard time intelligence in DAX - you can check the corresponding documentation for that.

## Big Stew said:

Yes i tried the above, but when i select Feb...it only gives me Feb sales count, not Feb and Jan combined....

## Marco Russo (SQLBI) said:

Check that you are using the right data model for Time Intelligence (Date should be a separate table)

## Big Stew said:

Hi...Tried date table...could not figure it out....it gave me the total but when i selected the month filters it did not add Jan and Feb when i selected Feb... also tried "=CALCULATE(

SUM(Transactions[actual]),

All(Transactions),

Transactions[month]<=EARLIER(Transactions[month])

)

"....as a calculated column...and it worked, but performance was poor...can this be converted to a measure....any additional help would be so appprrreeeciated.....i know its something simple i'm missing...

## Marco Russo (SQLBI) said:

The formula above should work if you have a correct data model. Try to see the examples included here:

http://www.daxpatterns.com/time-patterns/

## Big Stew said:

ok still not getting it...I have a column in the man table called Month End...there is only three dates in there now......i cREATED A DATE TABLE FROM JAN 2016 THRU MAR 2017..BY DAY.....SHOULD'NT AT LEAST THREE DATES BE RELATEABLE...???

## Marco Russo (SQLBI) said:

Try to read documentation about date table - it has to be a separate table with all days in a year, regardless data you link to.

## Big Stew said:

i tried this "CALCULATE(DISTINCTCOUNT([Global Id]),'WRKFR'[Termination Type]="RIF",DATESYTD('WRKFR'[Month End]))"...but it doesnt add months Jan and feb...when Feb is selected!!!

## Big Stew said:

i also tried "Measure 5:=CALCULATE(DISTINCTCOUNT([Global Id]),'WRKFR'[Termination Type]="RIF",FILTER(ALL('Calendar'[Dates]),'Calendar'[Dates]<=MAX('Calendar'[Dates])))"

## Marco Russo (SQLBI) said:

DATESYTD is designed to work with a date table, you are referencing a date column of the fact table, this will not work. Please, read documentation and follow best practice (create a separated date table) - also download examples from the link above.

## Marco Russo (SQLBI) said:

The second formula is a running total, not a YTD - please remember you have to use the date table in the pivot table, you should hide the column used in the relationship in the WRKFR table.

## Big Stew said:

This "CALCULATE(DISTINCTCOUNT([Global Id]),'WRKFR'[Term Type Count],FILTER(ALL('WRKFR'),COUNTROWS(FILTER('WRKFR',EARLIER('WRKFR'[MTH Number])<='WRKFR'[MTH Number]))))" works perfectly, however when i drop level 2 criteria in the table, its the same number for everybody.....what could i do different with this measure...to capture the breakout level 2 correctly...The total is correct but it does not break down the total by level 2 Row...

## Marco Russo (SQLBI) said:

By using FILTER ( ALL ( WRKFR ), ...

you are getting rid of all the filters so level 2 doesn't work for this reason.

As I said, you are trying to solve a problem in DAX when you have a problem in the data model, probably.

## Big Stew said:

Cool...I have a table with month end column that has three repeated dates for each employee..10,000 employees)..(month end Jan,feb,mar)...along with other columns, i created a calendar table.....what do you think is missing to achieve the before mention?

## Marco Russo (SQLBI) said:

Big, please, take a look at one of the many examples (of our book, of daxpatterns.com web site) to check whether your model correspond (date table with all dates in a calendar year, relationship with date column, data table correctly marked as date table...)

## Big Stew said:

just did...and got it....something simple i missed

## Big Stew said:

just did...and got it....something simple i missed

## Big Stew said said:

Can i ad a distinct id column to this dax for a ytd sum...but for distinct id's?????

CALCULATE (

SUM ( Transactions[Quantity] ),

FILTER (

ALL ( 'Date'[Date] ),

'Date'[Date] <= MAX ( 'Date'[Date] )

)

)

## Jos said:

Hi Marco,

What would be my calculation if I want to know how many deliveries I have on one day. I can use DISTINCTCOUNT([CustomerID]) but than powerpivot count the unique value`s not related to a date. I want powerpivot to count unique value`s on the same day. So when I select the pivot table on month basis I can see the number of deliveries in a month for each country. I hope you understand my problem.

CustomerID Route Shipdate Country

Reinrein BRU001 01/01/2016 Netherlands

Icesipp CGN002 01/01/2016 Germany

Hanot NZW002 15/01/2016 Netherlands

Rottstad BRU001 01/02/2016 Sweden

Rottstad BRU001 15/02/2016 Sweden

Hemlu CIA001 04/03/2016 Netherlands

Rottstad BRU002 04/03/2016 Sweden

Hemlu GOT002 16/04/2016 Netherlands

Osdrew GOT002 18/04/2016 Germany

## Marco Russo (SQLBI) said:

@Big: I don't understand your question - can you clariy?

@Jos: You should do a COUNTROWS ( DISTINCT ( SUMMARIZE ( fact, fact[customer], fact[deliverydate] ) ) )