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

     

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