Use time selector from Power BI as an argument in my SQL query

I have a simple query like :

SELECT COUNT(DISTINCT(p.id_organization)) AS Count
FROM dbo.paiement p
WHERE p.statut = 'AUTHORIZED'
      AND p.[date] > '2018-01-30'

I try to show this data on a PBI dashboard where I have a date range selector. I want the date to be fill with the begin and end date in two different areas so the calculation refresh on the fly. How can I link the AND p.[date] > 'XXXX-XX-XX' to this selector ?

EDIT

I just find out that I can do something like

Associations Actives = 
CALCULATE(
    DISTINCTCOUNT('BDD - Paiement'[id_organization]),
    FILTER(
        ALLSELECTED('Dates'[Date]),
        ISONORAFTER('Dates'[Date], MAX('Dates'[Date]), DESC)
    )
)

But I want to have a BETWEEN BeginDate AND EndDate corresponding the time slider

PS : How do you call this kind of query bellow ?

Answer

There are two approaches to this.

Import all the data you might need and do the filtering within Power BI.
If there’s too much data to import, you can create a DirectQuery. This will pass your slicer selections back to the server on the fly and automatically pull the bits it needs.

What you can’t do is pass slicer selections to your Query Editor connection string.

Advertisement

Answer