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