cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Responsive Resident
Responsive Resident

Cannot filter a measure - Help!

Hi all, hopefully someone has an idea for me here!

 

So i have the below data structure:

 

Year table:

Year
2019
2020

 

Month table:

YearMonth
20192019-01
20192019-02
20192019-03
20192019-04
20192019-05
20192019-06
20192019-07
20192019-08
20192019-09
20192019-10
20192019-11
20192019-12
20202020-01
20202020-02
20202020-03
20202020-04
20202020-05
20202020-06
20202020-07
20202020-08
20202020-09
20202020-10
20202020-11
20202020-12

 

Revenue table:

MonthRevenue
2019-01100
2019-01225
2019-05200
2019-05350
2020-01600
2020-011250

 

What I want to do is add the Year.Year column into the Page filters for the page, then filter to the year 2019 and return this:

 

MonthRevenue
2019-01325
2019-020
2019-030
2019-040
2019-05550
2019-060
2019-070
2019-080
2019-090
2019-100
2019-110
2019-120

 

I wrote a measure to try to achieve this:

 

 

 

 

RevenueMeasure =
VAR a = CALCULATE ( SUM ( Revenue[Revenue] ) ) 
RETURN 
IF ( ISBLANK ( a ), 0, a )

 

 

 

 

However this gives me an incorrect result:

 

MeasureFilteredData.jpg

 

Is there a different way to write this measure? I have to filter on the Year.Year column for the Page filter. See below for PBIX file:

 

https://1drv.ms/u/s!AuiIgc_S9J5Jhbpy9TdV7XyccnDgdg?e=o3gxxR

 

Cheers for all help
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Cannot filter a measure - Help!

Hi  @ansa_naz ,

 

Just redo your measure to:

 

 

RevenueMeasure =
VAR a =
    CALCULATE ( SUM ( Revenue[Revenue] ) )
VAR year_selection =
    SELECTEDVALUE ( 'Year'[Year] )
RETURN
    IF ( SELECTEDVALUE ( 'Month'[Year] ) = year_selection; a + 0; BLANK () )

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Cannot filter a measure - Help!

@ansa_naz 

There is no need of year table, join month year table with revenue on Month year and filter on year from that table it should work



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Responsive Resident
Responsive Resident

Re: Cannot filter a measure - Help!

I need the Year table to filter on other related tables, which are not relevant to the full question. Which is why I have to keep the data model as is, but still obtain required result. Any further help please?

Highlighted
Super User III
Super User III

Re: Cannot filter a measure - Help!

Hi  @ansa_naz ,

 

Just redo your measure to:

 

 

RevenueMeasure =
VAR a =
    CALCULATE ( SUM ( Revenue[Revenue] ) )
VAR year_selection =
    SELECTEDVALUE ( 'Year'[Year] )
RETURN
    IF ( SELECTEDVALUE ( 'Month'[Year] ) = year_selection; a + 0; BLANK () )

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Responsive Resident
Responsive Resident

Re: Cannot filter a measure - Help!

Brilliant thank you!

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors