Forum Discussion

gastonguy's avatar
gastonguy
Frequent Visitor
10 years ago
Solved

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 ex...
Need Help
  • Twan's avatar
    Twan
    10 years ago

    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's avatar
gastonguy
Frequent Visitor
10 years ago

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