How can I reference a Power BI measure in a SQL query?

Question

Long-time listener, first-time caller... :-)

So I'm new to Power BI and am hoping I can get this to work. I imported the SQL query below:

DECLARE 
    @RangeStart as varchar(19),
    @RangeEnd as varchar(19),
    @ReportStart as date,
    @ReportEnd as date

SET @RangeStart = '2020-01-01 00:00:00'
SET @RangeEnd = '2020-05-31 00:00:00'
SET @ReportStart = CAST(@RangeStart as datetime)
SET @ReportEnd = CAST(@RangeEnd as datetime)

SELECT COUNT(screen_client_id) AS SarpyYtdScreenings
FROM dbo.screen
WHERE (screen_date BETWEEN @ReportStart AND @ReportEnd)

So, from Power Query Editor, it "converts" it to:

let
    Source = Sql.Database("freedom1a", "pretrial", [Query="declare #(lf)@RangeStart as varchar(19),#(lf)@RangeEnd as varchar(19),#(lf)@ReportStart as date,#(lf)@ReportEnd as date#(lf)SET @RangeStart = '2020-01-01 00:00:00'#(lf)SET @RangeEnd = '2020-05-31 00:00:00'#(lf)SET @ReportStart = CAST(@RangeStart as datetime)#(lf)SET @ReportEnd = CAST(@RangeEnd as datetime)#(lf)SELECT        COUNT(screen_client_id) AS SarpyYtdScreenings#(lf)FROM            dbo.screen#(lf)WHERE        (screen_date BETWEEN @ReportStart AND @ReportEnd)"])
in
    Source

So far, so good. But I've created a "table" called DateParameters, with some measures that act like parameters for the user to use, like "EndDate" and "FirstDayOfYear". But I want to somehow pass these measures into my SQL query. I found a site that said I could replace the literal 2020-01-01 00:00:00 with the measure, but I can't get it to work. I've tried:

let
    Source = Sql.Database("freedom1a", "pretrial", [Query="declare #(lf)@RangeStart as varchar(19),#(lf)@RangeEnd as varchar(19),#(lf)@ReportStart as date,#(lf)@ReportEnd as date#(lf)SET @RangeStart = '"&DateParameters[FirstDayOfYear]&"'#(lf)SET @RangeEnd = '2020-05-31 00:00:00'#(lf)SET @ReportStart = CAST(@RangeStart as datetime)#(lf)SET @ReportEnd = CAST(@RangeEnd as datetime)#(lf)SELECT        COUNT(screen_client_id) AS SarpyYtdScreenings#(lf)FROM            dbo.screen#(lf)WHERE        (screen_date BETWEEN @ReportStart AND @ReportEnd)"])
in
    Source

...which almost works. When I start typing DateParameters, it knows what table I want, but no matter what measure in that table I reference, it doesn't recognize it. Unless my syntax is wrong. I also tried DateParameters.FirstDayOfYear, but it didn't like that, either. Can this even be done? Maybe it only recognizes table columns and not table measures? Do I need to create a column that simply holds the measure value? There must be a way for this to work.

Much appreciation for any help anyone can provide! Thanks.

Darwin

Assuming you are not using DirectQuery, the model contains all data and user selected parameters are applied to data stored in Power BI, not to the data import. You will use DAX to limit the data for the report from full data set. — Piotr

Alexis Olson Alexis Olson · Accepted Answer · 2020-06-23T20:59:23

You cannot use DAX measures or any tables/columns created with DAX within the Query Editor.

You've got a couple of options here.

Load all the dates/data you'll need for your report into your model and do your filtering within Power BI.
Use DirectQuery instead of loading all of the data into your model and Power BI will do the SQL filtering in the background.

How can I reference a Power BI measure in a SQL query?

1 Answers