Filter Context: let Date columns filter, but keep all names in table?

[Anonymous]

Hello! I've been trying solutions for this for the past few hours, finally thought to just try here. I have a relatively simple dataset, just like the sample data below: 

Customer Name	Date	Purchases
John	9/16/2022	14
Jacob	9/16/2022	5
Jingleheimer	9/16/2022	1
Schmitt	9/19/2022	2
Jacob	9/19/2022	1

I got a request from higher-ups to show the data in a simple table, Name in rows, Date in columns, sum Purchases in Values, and use the Relative Date filter (in the filter pane) to only show the next 12 months from 9/16/22 (today).

	Date
Name	9/16	9/19	9/25	9/26	9/31
John	14		2		2
Jacob	5	1		1
Jingleheimer	1
Schmitt		2

Here's the problem: if any Customers made a purchase outside that Date filter range, the customers just don't show up in the rows at left! ALL customers need to appear in the rows - even if there is no purchase data on that date for them. 

Of course, I did toggle the "Show items with no data" option, like in the screenshot below, but the customer names are still getting filtered out. 

What should be done? I have tried using ALL() or REMOVEFILTERS() in measures, but frankly keep getting errors I don't understand. Thank you in advance, and all help is greatly appreciated!

[PaulDBrown · Accepted solution]

Try using a measure along the lines of:

Purchases = SUM(Table[Purchases]) + 0

Or

Purchases =COALESCE ( SUM(Table[Purchases]), 0)

[parry2k]

@Anonymous the best practice/solution woule be create another table for customer (dimension) and link with transaction table and then use customer name from customer table and select show items with no data

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.