cancel
Showing results for
Did you mean:
Post Prodigy

## How can I use if conditions inside a table filter?

When I filter a table there are 2 conditions to filter by.  One is if the end date field is blank, and if it is, show me that row, and if it is not, show me the row that equals the dates i specify.

It works when there is no blank field, but it always shows me 2 rows (the blank row and the other row) when the blank field is present.

Here is my code:

FILTER(
SUMMARIZE(
Table,
Table[requestName],
Table[startDate],
Table[endDate],
Table[employeeName],
),
Table[employeeName] = " John Smith"

&&
(
if (

DATEVALUE(Table[endDate]) = blank(),

DATEVALUE(Table[endDate]) = blank()
AND(DATEVALUE(Table[startDate]) <= DATEVALUE("6/21/2021"),  DATEVALUE(Table[endDate]) >= DATEVALUE("6/21/2021"))
)
)

)

How can i structure if conditions in table filters to show it properly?

1 ACCEPTED SOLUTION
Super User III
``````
FILTER (
SUMMARIZE (
Table,
Table[request],
Table[startDate],
Table[endDate],
Table[employeeName],
Table[reason]
),
Table[employeeName] = " John Smith"
&& IF (
BLANK ()
IN CALCULATETABLE (
DISTINCT ( Table[endDate] ),
ALLEXCEPT ( Table, Table[EmployeeName] )
),
//if john smith has a row that contains a blank enddate
DATEVALUE ( Table[endDate] ) = BLANK (),
//Then ONLY show that row: req1
DATEVALUE ( Table[endDate] ) <> BLANK ()
&& AND (
DATEVALUE ( Table[startDate] ) <= DATEVALUE ( "6/21/2021" ),
DATEVALUE ( Table[endDate] ) >= DATEVALUE ( "6/21/2021" )
) //If it does not contain the blank enddate, then just show row req2
)
)``````

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

21 REPLIES 21
Super User III
``````Table[employeeName] = " John Smith"
&& IF (
VAR aux_ =
CALCULATETABLE (
DISTINCT ( Table[endDate] ),
ALLEXCEPT ( Table, Table[EmployeeName] )
)
RETURN
BLANK () IN aux_ || "No" IN aux_ ,``````

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Post Prodigy
Post Prodigy

Thank you that worked!

Super User III

It's not enough. Why would it? It's not the first filter argument, it's just a part of the condition you are building as second argument to FILTER(). Note the CALCULATE it is executed on its own and then its result used to build the result of the full condition. It's only affected by the row context (that will be converted into filter context through context transition). Imagine you had

FILTER(Table1, Table1[Name] = "John"  &&  Table1[Surname] = "Smith")

Would  the comparison Table1[Surname] = "Smith" be affected at all by Table1[Name] = "John" ?

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Post Prodigy

@AlB ,

So the calculatetable is affected by row context, and in my case the row context is also the the employee john smith.  Why do I still need AllExcept to work?

Super User III

Cause you have all these columns in your base table:

``````       Table[request],
Table[startDate],
Table[endDate],
Table[employeeName],
Table[reason]``````

The values in the the current row of all of them will be turned into filters through context transition. But you only want the filter on employeeName, right? So you get rid of all the others except for the employeeName. That is what the ALLEXCEPT does

I'm looking forward to some kudos on all these  answers 😉

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Post Prodigy

Thank you and last question.  Using calculatetable within the table filter requires me to access a table again on top of the table I am trying to filter.   Is this the most efficient way or best practice, or I guess the alternative method you proposed earlier was trying to fix that?  Kudos coming your way. Thank you so much! 😀

Super User III

Well, you do have to access the table anyway. To look at other rows for the current employee and see if there's a blank date. There's no avoding that; it is inherent to your requirement. I don't see a problem with it. It happens often with requirements of this type.

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Post Prodigy

Sorry I am stuck again..  What if i wanted to check if there is a Blank in the calculatetable or the text "No" in the same field?  I want to check a different field, not enddate anymore but this field can either be blank, No or Yes.  I tried putting || in there but i am not getting the desired results.  Hope you can help. Thanks.

Post Prodigy

@AlB Ok makes sense. Thanks for your help on this!

Super User III
``````
FILTER (
SUMMARIZE (
Table,
Table[request],
Table[startDate],
Table[endDate],
Table[employeeName],
Table[reason]
),
Table[employeeName] = " John Smith"
&& IF (
BLANK ()
IN CALCULATETABLE (
DISTINCT ( Table[endDate] ),
ALLEXCEPT ( Table, Table[EmployeeName] )
),
//if john smith has a row that contains a blank enddate
DATEVALUE ( Table[endDate] ) = BLANK (),
//Then ONLY show that row: req1
DATEVALUE ( Table[endDate] ) <> BLANK ()
&& AND (
DATEVALUE ( Table[startDate] ) <= DATEVALUE ( "6/21/2021" ),
DATEVALUE ( Table[endDate] ) >= DATEVALUE ( "6/21/2021" )
) //If it does not contain the blank enddate, then just show row req2
)
)``````

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Post Prodigy

@AlB Oh wow thank you that worked!  I never seen syntax like that before in an if statement.  Can you kind of walk me through the if statement?   You have the = blank() and <> blank() back to back which is unconventional from what i am use to seeing and it kind of throws me off trying to understand the logic.

Super User III

In fact there's no need for the  DATEVALUE ( Table[endDate] ) <> BLANK () in the third argument for the IF.  Remember the IF will be executed for each row of the table. First it checks if there is a BLANK() for the Employee in the current row.

- If there is, it checks if the current row has a null enddate. It it does the IF will return a TRUE and the row will be selected

- If there isn't, the third argument in the IF will be executed  and it will return a TRUE if the condition with the dates is met. If so, the IF will return a TRUE and the row will be selected. The DATEVALUE ( Table[endDate] ) <> BLANK () is superfluous here because we will only get here if there are no blank enddates for the current Employee

``````
FILTER (
SUMMARIZE (
Table,
Table[request],
Table[startDate],
Table[endDate],
Table[employeeName],
Table[reason]
),
Table[employeeName] = " John Smith"
&& IF (
BLANK ()
IN CALCULATETABLE (
DISTINCT ( Table[endDate] ),
ALLEXCEPT ( Table, Table[EmployeeName] )
),
//if john smith has a row that contains a blank enddate
DATEVALUE ( Table[endDate] )
= BLANK (),
//Then ONLY show that row: req1
AND (
DATEVALUE ( Table[startDate] ) <= DATEVALUE ( "6/21/2021" ),
DATEVALUE ( Table[endDate] ) >= DATEVALUE ( "6/21/2021" )
) //If it does not contain the blank enddate, then just show row req2
)
)``````

An alternative approach (check if it works):

``````VAR T1_ =
FILTER (
SUMMARIZE (
Table,
Table[request],
Table[startDate],
Table[endDate],
Table[employeeName],
Table[reason]
),
Table[employeeName] = " John Smith"
&& DATEVALUE ( Table[endDate] ) = BLANK ()
)
RETURN
IF (
NOT ISEMPTY ( T1_ ),
T1_,
FILTER (
T1_,
AND (
DATEVALUE ( Table[startDate] ) <= DATEVALUE ( "6/21/2021" ),
DATEVALUE ( Table[endDate] ) >= DATEVALUE ( "6/21/2021" )
)
)
)``````

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Post Prodigy

I think i get the logic now.  But in the blank() in calculatetable expression, it only builds a table for John Smith and not of all records in the table right?

-------------------

I tried the alternate approach and it was giving me an error:

It is referring to the filter function.

But the expression in the table filter, wouldn't it only either show a row with a blank enddate field, or nothing at all if the employee does not have a blank enddate field.  So it wouldn't execute the 3rd argument of the if statement?

Super User III

@wpf_ wrote:

I think i get the logic now.  But in the blank() in calculatetable expression, it only builds a table for John Smith and not of all records in the table right?

No. It builds it for the employeeName in the row under examination. FILTER walks over all rows of the table. Then the overall condition will only be met  for John Smith since you have a part of the AND on that

I'm just seeing the problem of the second approach is that IF can only return a scalar, not a  table. So forget about it. Plus it had another mistake anyway

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Post Prodigy

I was trying to see if i needed the allexcept function in there, but it turns out this is what makes it work.  Otherwise it would return both rows from John smith regardless if enddate is blank.  Why is that, i thought having john smith as the first filter argument would be enough?

Post Prodigy

@AlB Thank you so much for your help!

Super User III

Hi @wpf_

Not sure I understand but try this:

``````FILTER (
SUMMARIZE (
Table,
Table[requestName],
Table[startDate],
Table[endDate],
Table[employeeName]
),
Table[employeeName] = " John Smith"
&& (
IF (
Table[endDate] = BLANK (),
TRUE (),
AND (
DATEVALUE ( Table[startDate] ) <= DATEVALUE ( "6/21/2021" ),
DATEVALUE ( Table[endDate] ) >= DATEVALUE ( "6/21/2021" )
)
)
)
)``````

Why do you use the DATEVALUE on startDate and endDate? Are they not of type date already?

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Post Prodigy

What is the purpose of the true() expression?  It did not work.  When the enddate = blank, i want to filter by that blank row.  But currently it is showing 2 rows: the blank row and the non blank row.

Super User III

When the enddate = blank, i want to filter by that blank row.

What does that mean?

Show some sample data and explain the filtering you want to do based on that, showing the expected result. I don't understand what you need

 Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Announcements

#### Microsoft named a Leader in The Forrester Wave

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