Parameters Passing to dataset in Databricks Lakeview Dashboard

[Pragati_17]

I have a date range filter in Lakeview Dashboard and i want to distinct count number of months in selected date range filter and divide it with one of the columns and that column is used in counter viualization. But passing parameters is not possible to databricks. How to achieve this in Databricks Lakeview Dashboard. 

[Kaniz_Fatma]

Hi @Pragati_17, Let’s break down the steps to achieve this in Databricks Lakeview Dashboard:

1. Define Your Datasets:

   • Use the Data tab in your Lakeview dashboard to define the underlying datasets. You can define datasets as follows:
     • An existing Unity Catalog table or view.
     • A new query against one or more tables or views.
   • You can define multiple datasets by writing additional queries or selecting additional tables/views¹.

2. Create a Date Range Filter:

   • Click Filter to add a filter widget on your dashboard canvas.
   • From the Filter dropdown menu in the configuration panel, select Date range picker.
   • Select the Title checkbox to create a title field for your filter and rename it to "Date range"².

3. Distinct Count of Months:

   • To calculate the distinct count of months within the selected date range, you’ll need to create a new dataset or modify an existing one.
   • Write a query that extracts the month from your date column and performs a distinct count. For example:

     SELECT DISTINCT MONTH(date_column) AS month
     FROM your_dataset
     WHERE date_column BETWEEN start_date AND end_date
     

   • Replace date_column, your_dataset, start_date, and end_date with your actual column name, dataset, and date range filter values.

4. Column Division:

   • Assuming you have another column (let’s call it value_column) that you want to divide by the distinct count of months, create a new dataset or modify an existing one.
   • Write a query that calculates the sum of value_column and divides it by the distinct count of months:

     SELECT SUM(value_column) / COUNT(DISTINCT MONTH(date_column)) AS result
     FROM your_dataset
     WHERE date_column BETWEEN start_date AND end_date
     

   • Again, replace placeholders with your actual column names and filter values.

5. Create a Counter Visualization:

   • Use the Canvas tab to create a visualization that displays the result of the division (e.g., a counter).
   • Add the appropriate widgets and customize your visualization as needed.

6. Publish Your Lakeview Dashboard:

   • Once you’re satisfied with your dashboard, publish it to share informative data insights with your team.

Remember to adjust the queries and column names according to your specific use case. Lakeview dashboards allow you to manage permissions to datasets alongside visualization permissions,...¹.

Happy dashboarding! 📊🚀