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 IV
Super User IV

@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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.