Skip to main content
Showing results for 
Search instead 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

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,


Microsoft Employee
Microsoft Employee


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

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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