Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |