Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I would like the query for the data in my visualization to filter for different values in a field depending on the case specified in a parameter. What is the correct syntax?
Something like:
let
Source = Sql.Databases("RTPWDBDVIAN06"),
weatherDB = Source{[Name="weatherDB"]}[Data],
dbo_vw_WeatherData = weatherDB{[Schema="dbo",Item="vw_WeatherData"]}[Data],
CASE REGION
REGION = "PA"
#"Filtered Rows" = Table.SelectRows(dbo_vw_WeatherData, each ([WeatherStationCd] = "AOH" or [WeatherStationCd] = "ATL" ) and ([WeatherValueTypeCd] = "Temperature")),
REGION = "NY
#"Filtered Rows" = Table.SelectRows(dbo_vw_WeatherData, each ([WeatherStationCd] = "ALB" or [WeatherStationCd] = "BGM" ) and ([WeatherValueTypeCd] = "Temperature")),
in
#"Filtered Rows"
The value for the Parameter REGION would be specified somewhere before the query runs
Hi @Odilon,
Usually, we will do this with DAX rather than Power Query. In other words, we retrieve data from database and model it locally. The DAX formula could be like below.
Measure = SWITCH ( MIN ( 'table'[REGION] ), "PA", CALCULATE ( SUM ( Sales[Quantity] ), 'dbo_vw_WeatherData'[WeatherStationCd] IN { "AOH", "ATL" } ), "NY", CALCULATE ( SUM ( Sales[Quantity] ), 'dbo_vw_WeatherData'[WeatherStationCd] IN { "ALB", "BGM" } ) )
Best Regards,
Dale
Great! Thank you.
How do I specify REGION as a parameter the user "checks" to specify the Weather stations to which the visualizations are applied across the pages of the report?
Hi @Odilon,
You can either sync the slicers or use a Report Level filter. Please refer to the snapshot below.
Best Regards,
Dale
Thank you.
Once concern I have with defining a DAX measure restricted to a subset of data rather than restricting the data with the inititial Query is it seems in the first case I am "bringing" in all the data even though different users will only be using different subsets of the data.
Currently I have 2 reports, one for Region 1 and one for Region 2, associated with initital queries filtering to Region 1 or Region 2 Weather-Stations only.
The plus is users in each region only load data fro the report they need.
The disadvantage is I have to edit both reports every time I make a change.
Thoughts?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |