cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ChrislyBear Frequent Visitor
Frequent Visitor

Measure to Distinct Count by Category

Hi, 

 

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, 
       s.customer,
       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, 
                                  customer 
                           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: http://community.powerbi.com/t5/Desktop/Distinct-Count-by-Coulmn/m-p/106693#M44752

 

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,

Chris

1 REPLY 1
Moderator v-yuezhe-msft
Moderator

Re: Measure to Distinct Count by Category

@ChrislyBear,

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.

Regards,
Lydia

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

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 361 members 3,834 guests
Please welcome our newest community members: