Forum Discussion
Filter after grouping columns
I want to accomplish something easy to understand (and maybe easy to do but I can't find a way...).
I have a table which represent the date when a client has bought something.
Let's have this example:
Purchase_id | Purchase_date | Client_id
---------------------------------------------------------
1 | 2016/03/02 | 1
2 | 2016/03/02 | 2
3 | 2016/03/11 | 3
I want to create a single number card which will be the average of purchase realised by day.
So for this example, the result would be:
Result = 3 purchases / 2 different days = 1.5
I managed doing it by grouping in my query by Purchase_date and my new column is the number of rows.
It gives me the following query:
Purchase_date | Number of rows
---------------------------------------------
2016/03/02 | 2
2016/03/11 | 1
Then I put the field Number of rows in a single number card, selecting "Average".
I have to precise that I am using Direct Query with SQL Server.
But the problem is that I want to have a filter on the Client_id. And once I do the grouping, I lose this column.
Is there a way to have this Client_id as a parameter?
Maybe even the fact of grouping is not the right solution here.
Thank you in advance.
One way to get the number of days in the Purchase_date column would be to use the DISTINCTCOUNT() function. So DISTINCTCOUNT([Purchase_date]) should return the number of unique days in the data set. This will also work when you filter by Client_id. The result equation should look like this:
Result = COUNT([Purchase_date]) / DISTINCTCOUNT([Purchase_date])
Then you can add page or visual level filters to the report and filter the number card in the Filters section. Or you can write specific measures for different Client_ids like this:
Client 2 Result = CALCULATE([Result], Filter('YourTableName', 'YourTableName'[Client_id] = 2))
4 Replies
One way to get the number of days in the Purchase_date column would be to use the DISTINCTCOUNT() function. So DISTINCTCOUNT([Purchase_date]) should return the number of unique days in the data set. This will also work when you filter by Client_id. The result equation should look like this:
Result = COUNT([Purchase_date]) / DISTINCTCOUNT([Purchase_date])
Then you can add page or visual level filters to the report and filter the number card in the Filters section. Or you can write specific measures for different Client_ids like this:
Client 2 Result = CALCULATE([Result], Filter('YourTableName', 'YourTableName'[Client_id] = 2))Thank you for your answer.
I tried adding a column in my query, like this:
= Table.AddColumn(Source, "Result", each DISTINCTCOUNT([Purchase_date]))
The problem is that I get an error, saying that the name "DISTINCTCOUNT" is not recognized, and I should checked to it is well written.
I suppose it's because I am using Direct Query.
EDIT:
Sorry, I tried adding new measure and it worked like this. I don't know why it didn't work when I was in the Query Editor adding a custumized column...
Have a look at the link below to get an understanding of when you should uese measures or calculated columns
http://www.powerpivotpro.com/2013/02/when-to-use-measures-vs-calc-columns/
