DAX - Total number of orders per supplier on a between slicer

[erickung]

Hi,

On my dashboard I want to add a between slicer for my users to identify how many orders each supplier has completed.

My table have below columns

1. Order number

2. Vendor Name

3. Order Delivery Date

4. Order Requested Date

I want to be able to put on a slicer the count of order number, so they are able to filter by this to identify which supplier has 5 to 22 orders.

I want to add the date in here as well

My data goes all the way back 2006, and I want my users to be able to

1) filter by date, so they can filter to show 2022/23 financial year only and then

2) The between slicer for number of orders per supplier updates with the updated number based on the date slicer, and my users can filter using this slicer to see all suppliers with minimum 5 orders based on this period.

When dragging my number of orders to the slicer now, it only shows there is 1 order per supplier

but actually e.g. ABC company has 50 orders, XYZ has 30 orders etc.

I want my users to be able to filter using the slicer all suppliers with minimum 30 orders.

Is this possible to do?

What DAX do I have to create?  I understand I have to create a new column for this, but I do not know the DAX code for this.

Please help me, thanks!

[technolog]

You want to create a slicer that allows users to filter suppliers based on the number of orders they have within a specific date range. Let's break this down.

Firstly, you'll need a measure to count the number of orders for each supplier within the selected date range. This measure will be used to filter the data in the slicer.

Here's a DAX measure that counts the orders for each supplier within the selected date range:

OrderCountBySupplier =
CALCULATE(
COUNTROWS('YourTableName'),
FILTER(
'YourTableName',
'YourTableName'[Order Delivery Date] >= MIN('YourTableName'[Order Requested Date]) &&
'YourTableName'[Order Delivery Date] <= MAX('YourTableName'[Order Requested Date])
)
)
Replace 'YourTableName' with the actual name of your table.

Now, for the slicer to work as you want, you'll need to create a separate table that lists the number of orders (from 1 to the maximum number of orders any supplier might have). This table will be used for the slicer.

Here's how you can create this table:

OrderCountTable =
ADDCOLUMNS(
GENERATESERIES(1, MAXX(ALL('YourTableName'), [OrderCountBySupplier])),
"OrderCount", [Value]
)
Now, you can use this new table 'OrderCountTable' for your slicer. When users select a range, say 5 to 22, you can then filter your main table visualization using the OrderCountBySupplier measure to only show suppliers that have orders within that range.

For the date slicer, you can simply use the 'Order Delivery Date' column. When users select a date range, the OrderCountBySupplier measure will automatically adjust to count orders within that date range, and the slicer will update accordingly.