cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mattbarker
Regular Visitor

Use of filter condition in countrows when condition can be blank

I have a user slicer and a matrix table of values populated by a series of countrow conditions. I would like the table to be affected by the user selection but the user selection can also be blank, meaning "all".

I have tried including the selected user in the filter condition but as this appears blank when no user is selected, the filter does not bring back the correct number of rows.

 

Question. How can I handle a blank filter condition?

VAR CurrentStatus = SELECTEDVALUE('opportunities'[StepNameNumber])

VAR CurrentUser = SELECTEDVALUE('opportunities'[_owninguser_value])


VAR ThisStep =
COUNTROWS(
FILTER(
ALL('opportunities'),
'opportunities'[wvnz_processtype]=783110002 &&
'opportunities'[StepNameNumber]=CurrentStatus &&
'opportunities'[_owninguser_value]=CurrentUser
)
)
 
Filters seem to work well when the conditions always return a value but it seems like I need the user condition in the filter to be ignored completely
 
.Screenshot 2021-06-15 111046.jpg
2 REPLIES 2
amitchandak
Super User
Super User

@mattbarker , you can use isfiltered to check you have got some value of not

 

or try like

 

VAR CurrentStatus = values('opportunities'[StepNameNumber])

VAR CurrentUser = values('opportunities'[_owninguser_value])


VAR ThisStep =
COUNTROWS(
FILTER(
ALL('opportunities'),
'opportunities'[wvnz_processtype]=783110002 &&
'opportunities'[StepNameNumber] in CurrentStatus &&
'opportunities'[_owninguser_value] in CurrentUser
)
)


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

Proud to be a Super User!

Hi @amitchandak,
Thank you. I will investigate isfiltered. You could please give me an idea of how it is used in this context?

You said "or try like" but I did not see any change to my DAX. Did you mean to recommend a change to my DAX?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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!