Forum Discussion

BalajiBuddha's avatar
BalajiBuddha
Frequent Visitor
3 months ago
Solved

Native SQL query for Power BI

HI Everyone,

i want to filter out few things here 

1. for reporting period <=2026-03 i should show all data 

2. for reporting period >= 2026-04, for ssid - kunshan and takahama i should exclude few countries and for remaining SSID i should show all countires

Native SQL query for Power BI

Thanks,
Balaji.

 

Need Help
  • ArwaAldoud's avatar
    ArwaAldoud
    3 months ago

    Hi BalajiBuddha 
    Hi,
    There are two approaches you can use — pick whichever fits your setup better:
    Option 1: Native SQL Query inside Power BI Advanced Editor
    Pass the full SQL logic directly in M code so the filtering happens at the source before data loads into Power BI:

    let
    SqlQuery =
        "SELECT *
        FROM ""your_schema"".""your_table""
        WHERE
            reporting_period <= '2026-03'
            OR
            (
                reporting_period >= '2026-04'
                AND (
                    (
                        ssid IN ('Kunshan', 'Takahama')
                        AND country NOT IN ('Country1', 'Country2', 'Country3')
                    )
                    OR ssid NOT IN ('Kunshan', 'Takahama')
                )
            )",

    Source = Snowflake.Databases("your_server","your_warehouse"),
    DB = Source{[Name="your_database"]}[Data],
    Schema = DB{[Name="your_schema"]}[Data],
    Result = Value.NativeQuery(Schema, SqlQuery)

in
    Result

     

    Option 2:  Create a View in Snowflake (Recommended if you have access)
     This way every report using this dataset inherits the rules automatically with no changes needed in Power BI:

     

    If this response was helpful, please accept it as a solution and give kudos to support other community members

     

     

6 Replies

  • v-priyankata's avatar
    v-priyankata
    Community Support
    3 months ago

    Hi BalajiBuddha 

    Thank you for reaching out to the Microsoft Fabric Forum Community.

    SharmilaBrisca ArwaAldoud Thanks for the inputs.

    I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

     

    • v-priyankata's avatar
      v-priyankata
      Community Support
      3 months ago

      Hi BalajiBuddha 

      Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

  • SharmilaBrisca's avatar
    SharmilaBrisca
    Resolver I
    3 months ago

    Hi BalajiBuddha,

    If your database is sql server try the below query,

    SELECT *

    FROM YourTable

    WHERE

        CONVERT(DATE, ReportingPeriod + '-01') <= '2026-03-01'

        OR

        (

            CONVERT(DATE, ReportingPeriod + '-01') >= '2026-04-01'

            AND

            (

                (

                    SSID IN ('Kunshan', 'Takahama')

                    AND Country NOT IN ('Country1', 'Country2')

                )

                OR

                SSID NOT IN ('Kunshan', 'Takahama')

            )

        )

     

    Thanks and Regards

    GainInsights Solutions

    Trusted Microsoft Partner - https://gain-insights.com/partnerships/microsoft/
    www.gain-insights.com

    • BalajiBuddha's avatar
      BalajiBuddha
      Frequent Visitor
      3 months ago

      HI ,

      we are using snowflake but user asking us to do the changes in the dataset so , all related reports will effect , so i want this changes in dataset.


      • ArwaAldoud's avatar
        ArwaAldoud
        Super User
        3 months ago

        Hi BalajiBuddha 
        Hi,
        There are two approaches you can use — pick whichever fits your setup better:
        Option 1: Native SQL Query inside Power BI Advanced Editor
        Pass the full SQL logic directly in M code so the filtering happens at the source before data loads into Power BI:

        let
    SqlQuery =
        "SELECT *
        FROM ""your_schema"".""your_table""
        WHERE
            reporting_period <= '2026-03'
            OR
            (
                reporting_period >= '2026-04'
                AND (
                    (
                        ssid IN ('Kunshan', 'Takahama')
                        AND country NOT IN ('Country1', 'Country2', 'Country3')
                    )
                    OR ssid NOT IN ('Kunshan', 'Takahama')
                )
            )",

    Source = Snowflake.Databases("your_server","your_warehouse"),
    DB = Source{[Name="your_database"]}[Data],
    Schema = DB{[Name="your_schema"]}[Data],
    Result = Value.NativeQuery(Schema, SqlQuery)

in
    Result

         

        Option 2:  Create a View in Snowflake (Recommended if you have access)
         This way every report using this dataset inherits the rules automatically with no changes needed in Power BI:

         

        If this response was helpful, please accept it as a solution and give kudos to support other community members