Forum Discussion

wpf_'s avatar
wpf_
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

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.  ...
Need Help
Tips and Tricks
  • AlB's avatar
    AlB
    5 years ago

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

     

    		 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.

     

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

wpf_ 

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.