Forum Discussion

Odilon's avatar
Odilon
Frequent Visitor
8 years ago

SWITCH or CASE in Query for data

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

Need Help

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Odilon
      Frequent Visitor
      8 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi Odilon,

         

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

        SWITCH_or_CASE_in_Query_for_data

         

        Best Regards,

        Dale