Matrix filter only including child values

[mollycat]

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:

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]),

            ALLCROSSFILTERED('Attributes Table')

        ),

    NOT ISINSCOPE('Parent + Child Table'[CHILD ID]),

        CALCULATE(

            MAX('Attributes Table'[TITLE]),

            TREATAS(VALUES('Parent + Child Table'[PARENT_ID]),'Attributes Table'[PARENT ID]),

            ALL('Parent + Child Table'[PARENT ID])

    ))

Any help is GREATLY appreciated.

[v-nmadadi-msft]

Hi @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

[v-nmadadi-msft]

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 you

[v-nmadadi-msft]

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.

[mollycat]

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.

[v-nmadadi-msft]

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

[mollycat]

Hi @v-nmadadi-msft, your example here doesn't quite meet my requirements as the issue is related to Parent records being removed from the matrix who don't have any children. Here, P1 and P2 both have Child records, so it does not address my issue. Thank you for the response!

[v-nmadadi-msft]

Hi @mollycat ,


In that case, as suggested by @johnt75 and @DataVitalizer , consider restructuring the data model for example, redesigning tables and reviewing relationships. A tweaked model may make it easier to achieve the desired output without relying on complex workarounds.

Thank you

[johnt75]

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.

[mollycat]

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.

[johnt75]

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.

[mollycat]

@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. ☹️

[johnt75]

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.

[DataVitalizer]

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 💡

🟩 Follow me on LinkedIn

[mollycat]

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 🙂

[DataVitalizer]

@mollycat

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 💡

🟩 Follow me on LinkedIn

[mollycat]

Hi @DataVitalizer, unfortunately using a calculated column will not work for all of my filters because I have one that is required to be a measure.

It turns out the issue was less with the formula I was using and more with the way Power BI is flattening the matrix to apply the filter. As mentioned above, the DAX query indicates that SUMMARIZECOLUMNS is flattening the parent/child and when there are parent records in the matrix that do not have any child records, they are removed from the evaluation. My understanding is that there isn't any way that I can override this...

[DataVitalizer]

@mollycat a possible workaround is to adjust your data model or use disconnected tables to preserve parent rows, but it depends on your exact scenario.

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Follow me on LinkedIn