Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Using a list query as parameter file for dynamic table filtering

 

I need help with being able to use a list query value (reportEndDate) as a parameter (DataPullEnddate) to filter my Dimdate as shown in the M query below.

 

I observed that when the value of reportEnddate changes, parameter DataPullEndDate which derives its value from ReportEnddate remains static 9/30/2020 even after refresh.

 

I want the new date to take effect in PowerBI service automatically each time reportEndDate changes after refresh. 

 

See screenshots attached.      https://1drv.ms/b/s!AlMdRxAveLesgbQN1BnQwMjUIa97JA

 

 

let

    Source = Sql.Database("ebhreportingdev-dw.database.windows.net", "ebhreportingdw"),

    dw_dimDate = Source{[Schema="dw",Item="dimDate"]}[Data],

    #"Renamed Columns" = Table.RenameColumns(dw_dimDate,{{"calDate", "Calendar date"}}),

    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "calDayNo", each Date.Day([Calendar date])),

    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"dimDateKey", "Calendar date", "calMonthNo", "calDayNo", "calMonth", "calQuarter", "calYear", "currentCalDay", "currentCalMonth", "currentCalYear", "currentCalMTD", "currentCalYTD", "recordEffectiveFromDate"}),

    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Calendar date", "Date:"}}),

    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns1", each [#"Date:"] >= DataPullStartDate and [#"Date:"] <= DataPullEndDate)

in

    #"Filtered Rows1"

 

Link:

 

https://1drv.ms/b/s!AlMdRxAveLesgbQN1BnQwMjUIa97JA

 

3 REPLIES 3
lbendlin
Super User
Super User

" parameter DataPullEndDate which derives its value from ReportEnddate"

How?  You can overwrite parameters at runtime, but you cannot store that change in Power Query. Power BI has no memory of its own activities. Anything memory related has to happen outside of Power BI, for example with a second query where you fetch that ReportEndDate from the data source.

 

You can simplify your query a bit:

 

let
    Source = Sql.Database("ebhreportingdev-dw.database.windows.net", "ebhreportingdw"),
    dw_dimDate = Source{[Schema="dw",Item="dimDate"]}[Data],
    #"Renamed Columns" = Table.RenameColumns(dw_dimDate,{{"calDate", "Date:"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "calDayNo", each Date.Day([#"Date:"])),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [#"Date:"] >= DataPullStartDate and [#"Date:"] <= DataPullEndDate)
in
    #"Filtered Rows1"

 

Anonymous
Not applicable

@lbendlin 

 

Thank you for your response.   A quick solution to the issue would be answered as below:

 

In this line:

#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [#"Date:"] >= DataPullStartDate and [#"Date:"] <= DataPullEndDate)

 

What M query syntax will allow me replace param DataPullStartdate  with list value ReportstartDate and param DatapullEndDate with list value ReportEndDate?  In that case I wont need the param files after replacing with the list values.  

 

Those list values are from a backend table.  

 

Using paran files does not solve my proble as the list values do not autiomatically populate the param files. I intend to apply those values to dynamically  filter my DimDate without having to change the param values in Desktop.

 

Thank you for your usual quick response.

 Capture2.JPGCapture3.JPG

Not sure why you want to use lists for single values, but in that case you need to say List.First() to get to the value

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.