I have a simple data model consisting of two model tables, vw_business_date and fnc_cash_flow_report. The vw_business_date is partitioned by a (valuation) scenario and each row contains a business date, the previous day's business date, the latest business date from the previous month and the latest business date from the previous year. The fnc_cash_flow_report is a direct query of a SQL function query that takes as an input the scenariod id and the 4 business dates and returns the latest cash flow valuation for the business date plus change in valuation since the previous dates (i.e. day to day change, month to date change etc.).
I've set up M query parameters for the parameters passed to the function and bound them to the vw_business_date columns and then set up a slicer to select the scenario and the business date (i.e. a row in vw_business_date) and a matrix to display the sum cash flow values for this data.
However, when I select a buiness date the other bound parameters don't change (i.e. prev business date, etc.) so the wrong query is run.
Here's a few pictures to make things clearer (in the screen shot the business date is selected as the 10th Feb but the data is queried for a previous date of 5th Feb when it should be the 7th):
I am running direct queries against Databricks Delta Tables but I don't think that is the relevant factor as it seems to be an issue with parameter binding.
Can anyone explain why this doesn't work and perhaps suggest a workaround?
Much obliged,
David
