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:   l...
Need Help
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

      • Odilon's avatar
        Odilon
        Frequent Visitor
        8 years ago

        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?