Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
erickung
Frequent Visitor

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

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!

1 REPLY 1
technolog
Super User
Super User

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.