cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Measure with FILTER on [Date]

Hi all. I'm using the Timeline by Queryon visual to show the contents of a month-long course. I'd like to replace the Date field (under Date/Time in first screenshot), which includes weekends and holidays for ease of use by the authors of the source data, with a measure that only shows Dates that have a corresponding Class # (see 2nd screenshot), basically eliminating days with no class. Can't get my head around how to use FILTER and/or a conditional. Any advice out there?

 

calendar_question_1.PNG

calendar_question_2.PNG

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

Hi  @Anonymous ,

Try the following measure:

Class=if(max('Curriculum'[Class #])=blank(),"no class",max('Curriculum'[Class #]))

 

If this could not solve your questions,could you pls share your pbix file?Remember to remove confident data.

 

Best Regards

Lucien

View solution in original post

5 REPLIES 5
v-luwang-msft
Community Support
Community Support

Hi  @Anonymous ,

Try the following measure:

Class=if(max('Curriculum'[Class #])=blank(),"no class",max('Curriculum'[Class #]))

 

If this could not solve your questions,could you pls share your pbix file?Remember to remove confident data.

 

Best Regards

Lucien

View solution in original post

Anonymous
Not applicable

Thanks @v-luwang-msft ! Yes, this works. I can't get my head around why MAX is the answer, but it definitely is.

Anonymous
Not applicable

Thanks @amitchandak.
I want a permanent solution and I think a visual level filter is impermanent, right? I'm a newbie, so possibly very wrong.
I tried this...

ClassDates = FILTER('Curriculum'[Date], RELATED(NOT(ISBLANK('Curriculum'[Class #]))))
But it returns the error "A single value for column 'Date' in table 'Curriculum' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Which makes sense.
Maybe what I need is a dynamic table?
amitchandak
Super User
Super User

@Anonymous , add a visual level filter class is not blank

 

or add that to measure filter not(isblank(Table[Class]))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Anonymous
Not applicable

Thanks again @amitchandak . While the measure didn't work for me (could be user error), the visual level filter does seem to be a totally valid solution and would have sufficed if @v-luwang-msft hadn't provided another option. Much appreciated.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!