cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wpf_
Post Prodigy
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
AlB
Super User III
Super User III

@wpf_ 


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
        )
)

 

SU18_powerbi_badge

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.

 

View solution in original post

21 REPLIES 21
AlB
Super User III
Super User III

@wpf_ 

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

 

SU18_powerbi_badge

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.

 

wpf_
Post Prodigy
Post Prodigy

wpf_
Post Prodigy
Post Prodigy

@AlB 

 

Thank you that worked!

 

AlB
Super User III
Super User III

@wpf_ 

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" ?

 

SU18_powerbi_badge

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.

   

wpf_
Post Prodigy
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?

AlB
Super User III
Super User III

@wpf_ 

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 😉

 

SU18_powerbi_badge

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.

 

wpf_
Post Prodigy
Post Prodigy

@AlB 

 

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! 😀

AlB
Super User III
Super User III

@wpf_ 

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.

 

SU18_powerbi_badge

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.

 

wpf_
Post Prodigy
Post Prodigy

@AlB 

 

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. 

 

wpf__0-1624480276809.png

 

wpf_
Post Prodigy
Post Prodigy

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

AlB
Super User III
Super User III

@wpf_ 


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
        )
)

 

SU18_powerbi_badge

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.

 

View solution in original post

wpf_
Post Prodigy
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. 

AlB
Super User III
Super User III

@wpf_ 

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" )
            )
        )
    )

 

 

SU18_powerbi_badge

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.

 

 

 

 

 

wpf_
Post Prodigy
Post Prodigy

@AlB 

 

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:

wpf__0-1624380933240.png

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?

wpf__1-1624381106765.png

 

 

AlB
Super User III
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

 

SU18_powerbi_badge

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.

 

wpf_
Post Prodigy
Post Prodigy

@AlB 

 

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?

 

wpf__0-1624390754586.png

 

wpf_
Post Prodigy
Post Prodigy

@AlB Thank you so much for your help! 

AlB
Super User III
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?

 

SU18_powerbi_badge

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.

 

wpf_
Post Prodigy
Post Prodigy

@AlB 

 

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.  

AlB
Super User III
Super User III

@wpf_ 

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

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors