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
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
Thanks what should be SomeValue in 4th step above ?
Hi srlabhe , Thank you for the update.
In DAX, the MAX(Data[SomeValue]) is the value you want to return from that latest row such as metrics like Price, Balance, Quantity, a text column like Status, Category or an ID if you are returning identifiers.
For example:
MappedID as_of_date PM_IS_ACTIVE Amount
A1 2025-09-10 Y 100
A1 2025-09-15 Y 120
A1 2025-09-18 Y 140
B1 2025-09-12 Y 80
B1 2025-09-17 Y 90If your slicer selects '2025-09-16', you want to return the latest record ≤ that date.
Then MAX(Data[Amount]), returns 120 for A1 and 90 for B1, because those are the latest as_of_date ≤ 2025-09-16.I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
This LatestValue measure should be crated on LatestRecords data right ?
Hi srlabhe Yes, LatestValue measure should be created on LatestRecords data. Please do let us know if you have any further queries.
Regards,
Dinesh