Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
amaniramahi
Helper V
Helper V

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
Community Champion
Community Champion

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 still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

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 still way beyond their comprehension!

DAX is simple, but NOT EASY!

@CNENFRNL , no wonder you are a community champion! 

Thanks 

speedramps
Community Champion
Community Champion

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.