SWITCH or CASE in Query for data

[Odilon]

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

[v-jiascu-msft]

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

[Odilon]

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?

[v-jiascu-msft]

Hi @Odilon,

You can either sync the slicers or use a Report Level filter. Please refer to the snapshot below.

Best Regards,

Dale

[Odilon]

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?