cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amaniramahi
Resolver II
Resolver II

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" 

1 ACCEPTED SOLUTION
CNENFRNL
Super User
Super User

Screenshot 2021-11-21 161137.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

4 REPLIES 4
CNENFRNL
Super User
Super User

Screenshot 2021-11-21 161137.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

@CNENFRNL , no wonder you are a community champion! 

Thanks 

speedramps
Super User
Super User

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
 

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!