Forum Discussion

amaniramahi's avatar
amaniramahi
Helper V
4 years ago
Solved

Summarize and filter

I have a table contains the following columns in addition to many

 

The ActivityDate is used in a slicer

 

I need to calculate the disticntcount of Account names that have more than 1 activity subject to the selected period in ActivityDate slicer

 

Account NameActivityDate
Account A1/1/2020
Account A1/7/2020
Account A1/8/2020
Account A1/1/2021
Account B1/1/2019
Account B1/1/2020
Account B1/7/2020
Account C1/1/2018
Account C1/1/2020
Account C1/7/2020
Account C1/9/2020
Account C1/10/2020
Account D1/1/2020

 

Mainly I tried the following

 

 

SUMMARIZE(
            Activities,
            Activities[Account Name],
            "ActivitiesCount",COUNTA(KinzActivities[Account Name]
        )

but I didn't know how to filter the resulted table according to the calculated column "ActivitiesCount" 

Need Help

4 Replies

  • speedramps's avatar
    speedramps
    Super User
    4 years ago
    Try this and click leave kudos and accept the solution
     
    Yourmeasurename =
    CALCULATE    (
    DISTINCTCOUNT(Activities[Account Name]),
    REMOVEFILTERS(Activities[Account Name])
    )
     
    How it works:-
    When you create report by Activities[Account Date] and Activities[Account Name]
    then Power BI applies default filters to each row.
     
    The CALCULATE and REMOVEFILTERS commands remove the default Activities[Account Name] filter,
    but retain the default Activities[Account Date] filter.
     
    Thus returning an answer of 4 activies for 01/01/2000  and 3 for 01/07/2020
     
    • amaniramahi's avatar
      amaniramahi
      Helper V
      4 years ago

      Actually I dont see how it will work! 

      I need to calculate the accounts that have more than 1 activity within the a selectedperiod

      If I selected 2020 year, it should return 3 not 4