Forum Discussion
Matrix filter only including child values
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.
johnt75 Thank you for your reply... the problem is that I'm forced to use a measure rather than a calculated column for one of the filters because it is based on user-selected dates. My dataset contains Start and End dates and I require a measure to determine if the record is within range depending on the user-defined date filter. It works perfectly for all records that do have child values, but all parent records without children are automatically filtered out when the measure is applied to the matrix. ☹️
All I can suggest then is that you reexamine the logic needed to identify which nodes should be included or excluded, and see if there is a different way you can make the calculation, defining a measure specifically for use as a filter. Make sure that the measure returns 1 for rows which should be visible, not true, as you can't use true / false values in a measure as a filter.