Forum Discussion
Implementing Snowflake SQL in PBI Import(NOT DQ as its not suggested)
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 410I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
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
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