Forum Discussion

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
5 years ago
Solved

How to pass on a parameter to a SSAS query

Does anyone know how to pass on a parameter to a SSAS query?

My current query is following

 

AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/XYZ", "Budget", [Query="EVALUATE
VAR _1 = 2021
VAR _01 = 4
VAR _2 =
    FILTER (
        dx,
        dx[DataYear] = _1
            && dx[DataCAT] = "Forecast"
    )
............
........
", Implementation="2.0"])

 

I want to pass on a parameterized value to VAR _01 like following

 

let 
    var1=4,
    Source = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/XYZ", "Budget", [Query="EVALUATE
VAR _1 = 2021
VAR _01 = var1
VAR _2 =
    FILTER (
        dx,
        dx[DataYear] = _1
            && dx[DataCAT] = "Forecast"
    )
............
........
", Implementation="2.0"])
in 
  Source

 

Is it possible?

so far I have tried

 

let 
    var1=4,
    Source = AnalysisServices.Database("powerbi://api.powerbi.com/v1.0/myorg/XYZ", "Budget", [Query="EVALUATE
VAR _1 = 2021
VAR _01 = """&var1&"""
VAR _2 =
    FILTER (
        dx,
        dx[DataYear] = _1
            && dx[DataCAT] = "Forecast"
    )
............
........
", Implementation="2.0"])
in 
  Source

 

but it did not work.

Thank you in advance.

 

  • Found it

    var1=4,
    var2=Text.From(var1)
    VAR _01 = CONVERT("""&var2&""",INTEGER)

3 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion

    Found it

    var1=4,
    var2=Text.From(var1)
    VAR _01 = CONVERT("""&var2&""",INTEGER)
    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you for share the finding😉.

       

      Paul Zheng _ Community Support Team