Filter after grouping columns

[gastonguy]

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.

[Twan · Accepted solution]

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))

[gastonguy]

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...

[Twan]

@gastonguy Keep in mind that the query editor is a completely different thing than adding a calculated column or a measure.  The query editor uses a query language called M while calculated columns and measures use a language called DAX.  The query editor is good for mashing up data, changing data types and adding new derived columns.  Adding new columns using the query editor is similar to adding a calculated column but the query editor will perform better.

However, the problem you are trying to solve requires a measure.  This means that you cannot use the query editor and have to create a measure in the regular Power BI interface.  @itchyeyeballs posted a good article that explains the difference between calculated columns vs measures and when you should be using a measure.

[itchyeyeballs]

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/