Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Measure to Distinct Count by Category



I'm rather new to Power BI and I think I need some help. What I want to do is to distinct count a column using a measure, but the column should also be present in the report! The thing is, if I just put in a few columns, the column "sales[customer]" AND the measure "Need Help With This = DISTINCTCOUNT(sales[customer])" it will show a "1" in every row for the measure column, as expected. The measure I want SHOULD show the DISTINCTCOUNT as if the column "sales[customer]" wasn't present in the report.


I know, that this explanation isn't really understandable, so let me paint you a picture real quick (table structure is made up, but the real data is structured similarly).


Let's say we have a table of "sales" data. In this table there are, among other columns the columns "article", "customer", "date", "country" and a few aggregatable values like "sales amount" and so on.

Before creating the report I create two filters; one for "calendar[year]" and one for "sales[country]". Keep that in mind! The data will be already filtered! There's a relationship between my calendar table (date, year, month, day, calendar week, ...) and my sales table.


The (already filtered!) table could look like this if you just dragged the columns into a tabular report: 


article		customer	sales amount	...
1		a		234		...
1		b		4322		...
1		c		23		...
1		a		42		...
2		b		34		...
2		c		245		...
2		c		564		...
2		c		47		...
3		g		354		...
3		g		345		...
3		g		778		...
3		g		34		...


Now, if I used SQL I'd create the desired resulting report using the following query (the bold line represents the measure I'm looking for):


SELECT s.article,
       y.no_custmers as no_customers_per_article, 
       Count(s.*) AS no_of_rows_per_article_customer 
FROM   sales s 
       INNER JOIN (SELECT x.article, 
                          Count(x.customer) AS no_custmers 
                   FROM   (SELECT article, 
                           FROM   sales 
                           GROUP  BY article, 
                                     customer) x) y 
               ON s.article = y.article
group by s.article, s.customer


The resulting table would look like this:


article	no_customers_per_article	customer	no_of_rows_per_article_customer	
1	3				a		2
1	3				b		1
1	3				c		1
2	2				b		1
2	2				c		3
3	1				g		4


 I know that I'm putting in redundant data into my report, but that's the point!

I'd like to be able to filter out articles, that only one customer buys, for example.


Does anyone have an idea how to create such a measure? I already tried SUMMARIZE, GROUPBY combined with a NATURALINNERJOIN, and so on. No luck.


I got pretty far by using 

Need Help With This = CALCULATE(DISTINCTCOUNT(sales[customer])
            , ALLEXCEPT(sales, sales[article], calendar[year], sales[country]))

but this shows customers who haven't ordered anything in the filtered year, but in some other year! The value of the measure however is correct!?!

So the table report looks like this now:

article	no_customers_per_article	customer	no_of_rows_per_article_customer	
1	3				a		2
1	3				b		1
1	3				c		1
2	2				b		1
2	2				x		(Blank)
2	2				y		(Blank)
2	2				c		3
3	1				g		4

 I found this solution here:


The thing is, if the filtering would be static, then I'd already be done, because I'd create a calculated table or some calculated column with the filters already baked in. But we should be able to change the filtering from the report view! That's why I'm looking for a measure.


Best regards,


Moderator v-yuezhe-msft

Re: Measure to Distinct Count by Category


Could you please share sample data of sales table with date column and country column?  And please post the expected result when you filter year.


Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors