Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power BI Equivalent to SQL OR condition

I have a SQL statement that retrieves records based on this WHERE clause:

    WHERE a.postingdate >= '2018-01-01' OR  a.backoutdate >= '2018-01-01'

 

Then a have a disconnected date table where users can make their desired date selections via a slicer.  They are basically just filtering down the table of records returned above to their desired recordset.  It doesn't look like I can use the Filters pane, because it doesn't appear to allow the OR condition over two separate columns (postingdate OR backoutdate).

 

I thought a calculated column would do it, with syntax something like this:

    IF ([postingdate] >= DATE(2021,1,1) || [backoutdate] >= DATE(2021,1,1),TRUE,FALSE)
 
The thought now is I can replace the hardcoded Date values with user selected values from the slicer.
   Earliest Selected Date = MIN('Date'[Date])
 
However, the calculated column doesn't seem to have visibility to the Earliest Selected Date measure.  In data view, that measure gives me 1/1/2018, but in report view I can see in a card the same measure returns the correct 1/1/2021, which is the user selected value in the date table.
 
Is there a better way to do this?  Basically allowing the user to filter down a larger dataset using their desired date selection.
Need Help
  • MattAllington's avatar
    MattAllington
    5 years ago

    Interesting problem. A calc column won't work if you want the user to select the value. You will need to basically build a Union query to create the filter table and then return a result in a measure. You can place the measure in the table visual or possibly in the visual filters pane (if you don't want to see it in the table).  This should return 1 if the row meets the criteria  it assumes your visual has some row level primary key (or combination of columns that make it a pk)

    Show =
COUNTROWS (
    DISTINCT (
        UNION (
            FILTER ( table, table[date1] >= DATE ( 2020, 1, 1 ) ),
            FILTER ( table, table[date2] >= DATE ( 2020, 1, 1 ) )
        )
    )
)

     

1 Reply

  • MattAllington's avatar
    MattAllington
    Community Champion
    5 years ago

    Interesting problem. A calc column won't work if you want the user to select the value. You will need to basically build a Union query to create the filter table and then return a result in a measure. You can place the measure in the table visual or possibly in the visual filters pane (if you don't want to see it in the table).  This should return 1 if the row meets the criteria  it assumes your visual has some row level primary key (or combination of columns that make it a pk)

    Show =
COUNTROWS (
    DISTINCT (
        UNION (
            FILTER ( table, table[date1] >= DATE ( 2020, 1, 1 ) ),
            FILTER ( table, table[date2] >= DATE ( 2020, 1, 1 ) )
        )
    )
)