Forum Discussion
Filter after grouping columns
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))
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/
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.
