How to Display Top Categories in Databricks AI/BI Dashboard?

[Akshay_Petkar]

In a Databricks AI/BI dashboard, I have a field with multiple categories (e.g., district-wise sales with 50 districts). How can I display only the top few categories (like the top 10) based on a specific metric such as sales?

[Mo]

hello @Akshay_Petkar,

in your data tab, can't you create a dataset using "Create From SQL" and write a query like the following and use this dataset in your canvas?

SELECT COUNT(sales_key) total_sales, distirict FROM my_table

GROUP BY distirict

ORDER BY total_sales DESC

LIMIT 10

let me know if I get your question wrong 😉

[Akshay_Petkar]

Hello @Mo 

I am creating a AI/BI dashboard where I need to add multiple charts, each showing the top 10 values. For example, I used a query like select * from table and in the canvas, I added a chart with 50 unique values, but I want to display only the top 10. In another chart, there are 100 unique values, and again, I need to show the top 10 only. How can I apply this limit across all the charts?

[migq2]

I was having the same question and creating a new SQL dataset for this breaks cross filtering. Since the data is coming from different datasets cross filtering does not work nicely

Is there a more elegant solution to this?

[Mo]

hey @migq2 , @maks 

in the AI/BI dashboards in your data, add a limit parameter like:
select all from my_table limit :limit_number

to all your tables.

when you're on canvas and adding visualizations, add a filter and create a parameter with single value:

Then you can use that parameter to limit the numbers you're displaying for all the visuals that have that parameter in the limit. here I used the tables in samples.tpch and limit the numbers to 3 rows:

and this a sample query on orders table:

SELECT * FROM samples.tpch.orders limit :limit_number

let me know if this helps or you have doubts.

[Akshay_Petkar]

Hi @Mo ,

The parameter limits the rows from the dataset that we read in the query. For example, We create a city-wise sales chart at that time, it aggregates the values and performs a group by. This parameter will not perform on that. It will limit only the rows from the dataset

[erbg]

Did you find a solution? 

[GunaR]

i got a similar challenge of displaying top 10 rows based on a metrics? 

[BS_THE_ANALYST]

@GunaR is this not where we'd want to leverage a parameter? And then pair this with a filter on the dashboard? Perhaps you create an aggregate SQL query for the various metrics 

If you can't use a parameter in the ORDER BY clause, which you likely can't because you want a column name to to be output from the parameter, you could use something like this:

I think the CASE statement is a good workaround as a parameter will output a string field. The CASE statement then points it back to the column reference.

Make the parameter a choice and then the user will be able to select the appropriate metric to ORDER BY? In a LIMIT clause, you then just specify a numeric parameter with the appropriate number so they can limit top N

All the best,
BS 

[GunaR]

Thank you for the prompt response. 

Good that Parameter helps to make the sort dynamic, In my case i got a common dataset which get used across all the pages in the dashboard. Only few widgets need to limit the top 20 records ordered by certain column, rest all should return all the rows.