cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Apply filter to formula

Is there a way to add a formula filter for a certain year to the following equation? I have tried multiple ways but end up with errors. 

# Count = SUMX( VALUES( 'Table'[Event Title] )  ,CALCULATE(DISTINCTCOUNT( 'Table'[ID#] ) ))

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

5 REPLIES 5
Highlighted
Super User I
Super User I

So this measure reads seems like you're trying to sum the count of distinct table IDs, but only while using the [Event Title] column?? It's like you're asking "How many tables do we have? Sum all those numbers you just told me! Oh, and the only data I'm giving you to work with is the list of Event Titles!" That seems not right to me.  

 

I would start from scratch and re-write it like this for 2019:

# Count = CALCULATE( DISTINCTCOUNT('Table'[ID#]), FILTER('Table', YEAR('Table'[Date]) = 2019) )

Depending on what sort of visual you're putting this in, you may not have to specify all this.  If it's in a column chart with the year as an axis, PowerBI will automatically do this filtering for you if you set up the value of the bar to be a distinct count of IDs. More info on how you're using this and/or table structure would be helpful if this isn't the result you're looking for.

 

Highlighted

Maybe this will help explain what the desired outcome is



Date      Event Title     # Distinct Count          Week

1/1/19         A                  50                              1

1/3/18         B                  75                              1

7/4/19         X                  99                              4

7/4/18         V                  10                              4

TOTAL                              234

My formula is giving me the # Distinct count correctly, So the above table is fine and already in my report. Below that Table I want to place the following:

Week      Distinct Count 2018          Distinct Count 2019       Difference               %Change
1                         75                                      50                             -25                         -33.33%

4                         10                                      99                              89                           890%


Does that help?

Highlighted

What do you get if you just apply this:

Measure =
Calculate(
Distinctcount(Table1[ID]),
Calendar[Year] = "2019"
)

From my perspective you shouldnt need anything else with the data provided..


Connect on LinkedIn
Highlighted
Responsive Resident
Responsive Resident

Highlighted

This works beautifully thank you so much for your help!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors