Forum Discussion
Matrix filter only including child values
Hello,
I have a Matrix which has a Measure that calculates a value at both the Parent and Child levels:
| Record ID | Title (calculated via Measure) |
| Record A (Parent) | Title A |
| Record A expanded to show: Record B (Child) | Title B |
| Record B (Parent) | Title B |
In the Matrix, the Title is being reflected as expected, both for the Parent and Child records. But, if I apply a filter where Title = Title B, only Record A is returned. Record B at the Parent level is not listed. It seems the Measure filter is only being applied to the Child levels.
Measure:
21 Replies
Hi mollycat
I suggest modifying your measure to ignore filters on the Title column by adding REMOVEFILTERS('Attributes Table'[TITLE]) inside your CALCULATE calls, as shown below:Title =
SWITCH(
TRUE(),
ISINSCOPE('Parent + Child Table'[CHILD ID]) && MAX('Parent + Child Table'[CHILD ID]) <> BLANK(),
CALCULATE(
MAX('Attributes Table'[TITLE]),
TREATAS(VALUES('Parent + Child Table'[CHILD ID]), 'Attributes Table'[PARENT ID]),
REMOVEFILTERS('Attributes Table'[TITLE])
),
NOT ISINSCOPE('Parent + Child Table'[CHILD ID]),
CALCULATE(
MAX('Attributes Table'[TITLE]),
TREATAS(VALUES('Parent + Child Table'[PARENT_ID]), 'Attributes Table'[PARENT ID]),
REMOVEFILTERS('Attributes Table'[TITLE]),
ALL('Parent + Child Table'[PARENT ID])
)
)Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡Hello DataVitalizer, I appreciate the response! Unfortunately, it is still not working as expected. Using my original Measure (TITLE_CALC in this image), both the Parent and Child values are labelled as expected. After adding REMOVEFILTERS copied from your Measure (TITLE_Meas), the Child values are no longer being brought over:
Which might be okay if the filter was working, but when I filter for TITLE_Meas = Title B, no records are returned (again, I am assuming that the filter is searching only for Child-level records for some reason):
Thanks in advance for any additional guidance 🙂
I’ve identified the source of the issue filtering on a measure using REMOVEFILTERS causes problems with parent-child data relationships.
A reliable fix is to create a calculated column that retrieves the Title for each row, like this:
Title Calc Column =
VAR IsChild = NOT(ISBLANK('Parent + Child Table'[CHILD ID]))
RETURN
IF(
IsChild,
CALCULATE(
MAX('Attributes Table'[TITLE]),
TREATAS(VALUES('Parent + Child Table'[CHILD ID]), 'Attributes Table'[PARENT ID])
),
CALCULATE(
MAX('Attributes Table'[TITLE]),
TREATAS(VALUES('Parent + Child Table'[PARENT_ID]), 'Attributes Table'[PARENT ID])
)
)Use this column to filter your visual instead of the measure.
Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡
You can use Performance Analyzer to get the query behind the matrix. Run it in DAX Query View, or DAX Studio, and that will show you at what point the filter is being applied. You can also examine the values of the various variables which make up the query to see the intermediate results.
Hi johnt75, this was very helpful, thank you! I've now identified the problem but am unsure how to resolve it...when the filter is applied, I can see the DAX query is performing a SUMMARIZECOLUMNS which is effectively flattening the Parent/Child and taking the TITLE value from the Child:
We can see that Record B (parent with no children) is not being included in this evaluation, because it has no Child value. If I comment out the Child value from the SUMMARIZECOLUMNS evaluation, Record B is returned:
If Power BI is doing the flattening via SUMMARIZECOLUMNS behind the scenes, I'm not sure how to make sure that the records without children will be included...
Thank you for any additional help.
Given the way that the query is being formulated, using the title measure as a filter clearly won't work, as you cannot change the underlying query.
I would try to think of some other way that you can identify which rows should be filtered out. Ideally it would be based on a column in the model, either an existing one or you could create a new column specifically for this. Columns are better for filtering as the values are unambiguous whereas measures, as you have seen, can deliver unexpected results when used as filters.
Hi mollycat
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hi v-nmadadi-msft, thanks for following up. Unless I have missed something, based on what I've learned, it seems that what I'm trying to accomplish is not possible given how the underlying Power BI query is processing the matrix filters. The Performance Analyzer indicates that the parent/child hierarchy is being flattened via SUMMARIZECOLUMNS, which is not allowing my Measure filter to work as intended. If there is any way to work around this, or something that I have incorrectly interpreted, I would appreciate any further guidance.
Hi mollycat ,
Thanks for reaching out to the Microsoft fabric community forum.
One work around you could try is to create a new table called sortorder which contains numbers corresponding to child ID’s.
You could use that sortoder to now filter the matrix
Use is less than while filtering
Here is the output
Attaching .pbix file for reference.I hope this information helps. Please do let us know if you have any further queries.
Thank you
Hi mollycat
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank youHi mollycat ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.
Thanks and regards
Hi @v-nmadadi-msft, unless I have missed something, based on what I've learned, it seems that what I'm trying to accomplish is not possible given how the underlying Power BI query is processing the matrix filters. The Performance Analyzer indicates that the parent/child hierarchy is being flattened via SUMMARIZECOLUMNS, which is not allowing my Measure filter to work as intended. If there is any way to work around this, or something that I have incorrectly interpreted, I would appreciate any further guidance. With this being case, there isn't a viable solution.
Hi mollycat ,
You’re running into a limitation of the native Waterfall visual, it only supports sorting breakdown categories by value, not by a fixed order. That’s why concatenating Category + Breakdown into a measure works for sorting but breaks the structure.
If the required feature is important for your functionality, Please consider sharing your suggestion in the Power BI Ideas forum
Fabric Ideas - Microsoft Fabric Community
where the product team actively monitors user feedback. Ideas with strong community support are more likely to be considered for future implementation. Posting there helps ensure your request reaches the right audience and contributes to shaping the product roadmap.
Thank you
