SSAS + Query folding + change type to datetime

Hi ivostammis 

Looks like I'm trying to solve exactly the same problem.

I managed to get query folding to work but still struggling to get IR to work with SSAS.

What I did was use the hint in the doco about converting integers to work with the DateTime parameters and extended this concept  to work with SSAS returning dates in text type.

Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Learn

I noticed that the native query for SSAS would look like DateTime :

[Calendar].[Fiscal Month].&[2021-07-01T00:00:00]

despite the Power Query preview showing text:

Here is my M code for this function:

let
    Source = (x as datetime) => 
        let
            Day = if Text.Length(Number.ToText(Date.Day(x))) <2 then Text.Combine({"0",Number.ToText(Date.Day(x))}) else Number.ToText(Date.Day(x)),
            Month = if Text.Length(Number.ToText(Date.Month(x))) <2 then Text.Combine({"0",Number.ToText(Date.Month(x))}) else Number.ToText(Date.Month(x)),
            Year = Number.ToText(Date.Year(x))
        in
             Text.Combine({Year,"-",Month,"-",Day,"T00:00:00"})

in
    Source

and how i reference the function in the query:

let
    Source = AnalysisServices.Database("MySSAS", "My Data", [TypedMeasureColumns=true, Implementation="2.0"]),
    Model1 = Source{[Id="Model"]}[Data],
    Model2 = Model1{[Id="Model"]}[Data],
    #"Added Items" = Cube.Transform(Model2,{{Cube.AddAndExpandDimensionColumn, ....
    ........
    #"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([Calendar.Fiscal Month]) > "[Calendar].[Fiscal Month].&["&DateText(RangeStart)&"]" and Cube.AttributeMemberId([Calendar.Fiscal Month]) <= "[Calendar].[Fiscal Month].&["&DateText(RangeEnd)&"]"))
in
    #"Filtered Rows"

This all works in Power Query. The data preview is corectly filtered by the dates i set up in the RangeStart and RangeEnd parameters.

I tried a simple test that should just return one row per month and then set IR to retain 12 months of data (so a 12 row dataset) but it times out once i publish and refresh from the service. 

I've troubleshooted this to the nth degree already adn read all the docs and guy in a cube vids but still can't get it to work 😞

I suspect that QF is not actually taking place despite being able to "View Native Query" on my last step.

...stumped for now

Hopefully the above helps someone to figure this out and report back here 🙂

Did you try DateTime.FromText?

--Nate