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
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
" 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"
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
107 | |
105 | |
89 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |