How to slice table visual for values that don't exist in a range

[rahkim]

Greetings!

I have two tables in Power BI.  One has Employees with their assigned Customer.  The other table has the Customer, OrderID and Submitted date of the order.  Actually, I have some more fields and other tables (like Customers), but my issue is with trying to have a visual that shows all of the employees, and the number of orders they have with a customer during a date range - including the customers that aren't in the date range.  For example, Employee John has three Customers (A, B, C) assigned to them in the EmployeeCustomer table and Customer A has 3 orders in January, B has 4 in February and C has 0 orders.   I want the report to have date slider and a table visual so that when the user slides the date range to include January and February the table shows:

Employee	Customer	Order Count
John	A	3
John	B	4
John	C	0

If the user selects just January, I would want the table visual to show this:

Employee	Customer	Order
John	A	3
John	B	0
John	C	0

Currently, I can only show if there is an order in the date range.  I want to include customers that don't have an order in the date range.

Thanks!

[amitchandak]

@rahkim , Add +0 to your measure or try this option on customer

[rahkim]

Hi @amitchandak,

Those didn't work, unfortunately.  Here is the basic table structure:

EmployeeCustomer

Employee	Customer	Year
John	A	2020
John	B	2020
John	C	2020

Order

ID	Customer	OrderDate
1	A	1/1/2020
2	B	2/1/2020
3	A	1/2/2020

If the user drags the slider to Jan 1, then I want the table to look like:

Employee	Customer	Orders
John	A	2
John	B	0
John	C	0

If they drag to include Jan 1 - Feb 15 then I want the table to look like this:

Employee	Customer	Orders
John	A	2
John	B	1
John	C	0

I had some success when I just joined based on the year the employee had the customer and the year on the order date.  But if the user dragged the slider from Feb to March, it would not show A has having 0 orders.  It would only show C has having no orders because it was looking at the whole year and not the month/day of the order.

Would a lookup of some sort work?  

[v-jingzhang · Accepted solution]

Hi @rahkim 

You could add a Calendar table in your model. Then create relationships Calendar[Date] - Order[OrderDate] (1:*) and EmployeeCustomer[Customer] - Order[Customer] (1:*). And create a measure NumberOfOrder = COUNT('Order'[ID]) + 0 to count the number of orders.

Kindly let me know if this works.

Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[rahkim]

Hi @v-jingzhang 

Yes, that seems to work.  I had already started going down that path, but it seemed weird to create a calendar table to do this.  It works though.  Thanks!