Forum Discussion

srlabhe's avatar
srlabhe
Super User
10 months ago
Solved

Implementing Snowflake SQL in PBI Import(NOT DQ as its not suggested)

Hi All,

Actually I am trying to implement below query in Power BI Import(As dont wana use DQ with Dynamic parameter passed)

SELECT Table1.*,'mapped',
FROM Table1
WHERE as_of_date <= '2025-09-17'
QUALIFY ROW_NUMBER() OVER (
PARTITION BY MappedID
ORDER BY as_of_date DESC
) = 1
AND PM_IS_ACTIVE = 'Y' AND DM_IS_ACTIVE = 'Y' AND RM_IS_ACTIVE = 'Y' AND DESK_IS_ACTIVE ='Y'

UNION ALL

SELECT Table2.*,'unmapped',
FROM Table2
WHERE as_of_date >= '2025-09-17'
QUALIFY ROW_NUMBER() OVER (
PARTITION BY UnamppedID -- This is different field than MappedID used on top query
ORDER BY as_of_date DESC
) = 1
AND DM_IS_ACTIVE = 'Y' AND DESK_IS_ACTIVE ='Y'

Here '2025-09-17' is the Date selected in Slicer on report level which plays very important part.

Any suggestions how to implement this in PBI Import report as above query produces apprx 5M erecs.

Thanks

Need Help
  • v-dineshya's avatar
    v-dineshya
    10 months ago

    Hi srlabhe ,

    Thank you for the update. You want all latest dates(as_of_date) records for any Mapped ID/Unmapped ID. Please refer below output snap and attached PBIX file.

     

     

    In Slicer select on "2025-09-17", it will display the mapped records (latest date <= slicer date).

     

    EntityID       Dates <= 2025-09-17      Latest Date      Value
    A1                         09-10, 09-15               09-15            110
    A2                         09-12, 09-17               09-17            210


    In Slicer select on "2025-09-17", it will display the unmapped records (Earliest Date >= slicer date).

     

    EntityID             Dates >= 2025-09-17        Earliest Date    Value
    B1                           09-17, 09-19, 09-21       09-17              300
    B2                           09-18                             09-18              410

     

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

     

11 Replies

  • Irwan's avatar
    Irwan
    Super User
    10 months ago

    hello srlabhe 

     

    you can get data then choose snowflake.

     

    Put the snowflake server and warehouse (and database if you have).

    then you can paste your sql logic above in sql statement.

    after that, you will be asked to choose import or DQ.
    Choose import if you dont want to use DQ.

     

    Hope this will help.

    Thank you.

    • srlabhe's avatar
      srlabhe
      Super User
      10 months ago

      You didnt get the question, I need the date to be selected on report and then it shoul dtake latest date before selected date on report. 

      If I paste query as it is it woul dgive me latest for 09/172025 , but this is just an example i am giving here while I want date to be selected in report.

      While I know how to create a report in PBI 

      • v-dineshya's avatar
        v-dineshya
        Community Support
        10 months ago

        Hi srlabhe ,

        Thank you for reaching out to the Microsoft Community Forum.

         

        Power BI Import mode loads all data during refresh, slicers and visuals only filter what’s already loaded. You cannot directly pass slicer values into SQL in Import mode. So your slicer value like '2025-09-17' cannot dynamically filter SQL during report interaction, it only works during refresh.

         

        Please try below steps.

         

        1. Instead of trying to insert the slicer date into SQL, import recent data like last 12 months or 2 years of data from both tables. Please refer below sample Snowflake SQL.

         

        SELECT
        t.*,
        'mapped' AS type
        FROM Table1 t
        WHERE as_of_date >= DATEADD(year, -2, CURRENT_DATE())
        AND PM_IS_ACTIVE = 'Y' AND DM_IS_ACTIVE = 'Y'
        AND RM_IS_ACTIVE = 'Y' AND DESK_IS_ACTIVE = 'Y'

         

        UNION ALL

         

        SELECT
        t.*,
        'unmapped' AS type
        FROM Table2 t
        WHERE as_of_date >= DATEADD(year, -2, CURRENT_DATE())
        AND DM_IS_ACTIVE = 'Y' AND DESK_IS_ACTIVE = 'Y'

         

        Note: This gives you a dataset with all recent records that you can filter efficiently in Power BI without querying Snowflake each time.


        2. Create a Date dimension table in Power BI and mark it as a date table.

         

        3. Create a calculated table to pull Latest Record as of Selected Date. You can replicate your QUALIFY ROW_NUMBER() logic in DAX.


        LatestRecords =
        VAR SelectedDate = SELECTEDVALUE(Date[Date])
        RETURN
        FILTER (
        ADDCOLUMNS (
        Data,
        "@Rank",
        RANKX (
        FILTER (Data, Data[MappedID] = EARLIER(Data[MappedID]) && Data[as_of_date] <= SelectedDate),
        Data[as_of_date],
        ,
        DESC
        )
        ),
        [@Rank] = 1
        )


        4. Filter Logic in Measures for visuals.

         

        Latest Value =
        VAR SelectedDate = SELECTEDVALUE(Date[Date])
        RETURN
        CALCULATE (
        MAX(Data[SomeValue]),
        FILTER (
        Data,
        Data[as_of_date] <= SelectedDate &&
        Data[as_of_date] =
        CALCULATE (
        MAX(Data[as_of_date]),
        FILTER (Data, Data[MappedID] = EARLIER(Data[MappedID]) && Data[as_of_date] <= SelectedDate)
        )
        )
        )


        5. You have approximately 5M records, to optimize the performance, import only relevant columns, use numeric surrogate keys like integer IDs, disable auto date/time in model settings and use incremental refresh in Fabric or Power BI Premium, to avoid full refreshes.

         

        I hope this information helps. Please do let us know if you have any further queries.

         

        Regards,

        Dinesh