The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello, when I create a many to many relationship with 3 tables, I have a problem when I add a filter in power query.
Relationship
Result 1 without a filter in power query: Everything is normal
Result 2 with a date filter (after 2018) in power query: A blank row appears.
I do not understand de logic or the reason why the blank row appears. Any idea?
Thank you
Solved! Go to Solution.
Hello, yes I know that I can filter directly with a slicer. I was just wondering if it is possible to filter directly in PQ to avoid loading millions of lines. However, it seems like filtering in PQ only applies to one table and not to the other tables linked to it. Thank you for your help!
@11097486 what you mean by the filter in PQ? It shows blank coz value doesn't exist in the operational table and probably there are transactions in operations products.
Also why you have a cross filter to both between products and operational products table
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.
Hello,
1) I have a cross-filter because I want to be able to see the product codes linked to the operation codes.
2) I filter in PQ beaucoup I have millions of operation and I don't need to upload data from 2015 when I refresh my report.
Here's a simpler example. I removed 2020-01-01 with a filter in PQ. I just don't understand why it turns to Blank in the matrix. It souldn't since there is no blank dates in PQ, no?
Thank you
Hi, @11097486
It’s my pleasure to answer for you.
According to your description, Even if you filter 2020/1/1 of Table 1 in PQ, but the 'operation id' corresponding to the date of 2020/1/1 in Table 2 is still there, then Table 3 will generate three values of AA, BB, and DD according to the filtering relationship, So there is a value even if it is blank.
Like this:
You can directly use a date slicer or filter date in filter pane.
Like this:
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, yes I know that I can filter directly with a slicer. I was just wondering if it is possible to filter directly in PQ to avoid loading millions of lines. However, it seems like filtering in PQ only applies to one table and not to the other tables linked to it. Thank you for your help!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
95 | |
80 | |
62 | |
56 |
User | Count |
---|---|
252 | |
121 | |
112 | |
81 | |
70 |