cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

How to Dynamically select the parameter for Advance query from Publish Report

How to Run Time (While Publish) the select the custome Parameter from Parameter List using the Advance Editors in Power BI.

Actually I am importing the data (Events log) from Azure Applicationinsight (like device Id, Date, Version Number, Status etc). The query has created by Export function in Azure and implemented by BlankQuery as data source in Power BI ( sample query has as mentioned below).

 

Now I would like to select the records by dynamically (Desktop and Web browser) change the duration from Parameter list. When I Publish it then their have no option for parameter list

 

 

Query 1.

 

let AnalyticsQuery =

let Source = Json.Document(Web.Contents("https://api.applicationinsights.io/v1/apps/XXXXXXXXXXXXXXXXXXXX/query",

[Query=[#"query"="customEvents

| where name == 'Devices'

| project custDim = parse_json(tostring(customDimensions))

| project Properties = tostring(custDim.Properties), tostring(custDim.IngressTimestamp)",#"x-ms-app"="AAPBI",#"timespan"=Text.From(Timefrom) ,#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),

TypeMap = #table(

{ "AnalyticsTypes", "Type" },

{

{ "string",   Text.Type },

{ "int",      Int32.Type },

{ "long",     Int64.Type },

{ "real",     Double.Type },

{ "timespan", Duration.Type },

{ "datetime", DateTimeZone.Type },

{ "bool",     Logical.Type },

{ "guid",     Text.Type },

{ "dynamic",  Text.Type }

}),

DataTable = Source[tables]{0},

Columns = Table.FromRecords(DataTable[columns]),

ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),

Rows = Table.FromRows(DataTable[rows], Columns[name]),

Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))

in

Table

in AnalyticsQuery

 

 

I have created the Parameter List #"timefrom" in Power BI.  and replace the hardcoded value from Parameter name #"timespan"=Text.From(Timefrom)

 

Parameter List :

Timefrom(PT12H)

 

Last Hour

4 hours

12 hours

24 hours

48 hours

3 days

7 days

30 Days

90 Days

         

PT1H

PT4H

PT12H

PT24H

PT48H

P3D

P7D

P30D

P90D

 

"PT12H" meta [IsParameterQuery=true, List={"PT1H", "PT2H", "PT12H", "PT24H", "P3D", "P7D"}, DefaultValue="PT1H", Type="Text", IsParameterQueryRequired=true]

 

Output :

susheeltyagi_0-1597067449241.png

 

susheeltyagi_0-1597068301304.png

 

I am new to Power Bi. Please help me.

 

Thanks

 

Susheel

0 REPLIES 0

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors