Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to Solution.
Try using a measure along the lines of:
Purchases = SUM(Table[Purchases]) + 0
Or
Purchases =COALESCE ( SUM(Table[Purchases]), 0)
Proud to be a Super User!
Paul on Linkedin.
Try using a measure along the lines of:
Purchases = SUM(Table[Purchases]) + 0
Or
Purchases =COALESCE ( SUM(Table[Purchases]), 0)
Proud to be a Super User!
Paul on Linkedin.
@JPBIToday 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
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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
86 | |
75 | |
73 | |
70 | |
57 |
User | Count |
---|---|
98 | |
97 | |
92 | |
78 | |
70 |