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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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