Filtering a non-aggregated matrix with a measure

[mollycat]

Hello,

I have a matrix which I have configured to maintain both parent- and child-level attributes (rather than aggregating). Sample:

Record ID	Title_Calc	Start Date_Calc	End Date_Calc
Record A (here acting as parent)	Title A	1/1/2025	1/8/2025
(Record A expanded) --> Record B (child of Record A)	Title B	1/2/2025	1/3/2025
(Record A expanded) --> Record C (child of Record A)	Title C	1/12/2025	1/20/2025
Record B (here acting as parent)	Title B	1/2/2025	1/3/2025
Record C (here acting as parent)	Title C	1/12/2025	1/20/2025

I am using the following measure formula to return the parent/child attributes for the Title, Start Date, and End Date:

TITLE_CALC =

SWITCH(

    TRUE(),

    ISINSCOPE('Table of Parent/Child Record Pairs'[ChildRecordID]) && MAX('Table of Parent/Child Record Pairs'[ChildRecordID])<>BLANK(),

        CALCULATE(

            MAX(PrimaryTable[TITLE]),

            TREATAS(VALUES('Table of Parent/Child Record Pairs'[ChildRecordID]),PrimaryTable[RecordID]),

            ALLCROSSFILTERED(PrimaryTable)

        ),

    NOT ISINSCOPE('Table of Parent/Child Record Pairs'[ChildRecordID]),

        CALCULATE(

            MAX(PrimaryTable[TITLE]),

            TREATAS(VALUES('Table of Parent/Child Record Pairs'[ParentRecordID]),PrimaryTable[RecordID]),

            ALL('Table of Parent/Child Record Pairs'[ParentRecordID])

    ))

Given this formula, if I apply a filter for the TITLE from the ParentTable, the parent-level records with that title (and all of their children, even if they do not meet the condition) are being returned in the matrix as expected. However, if I try to filter on a Measure, the matrix is not filtering correctly. I have a Slicer that has a date range calculated from a Date table. I would like to be able to use the date Slicer to then filter the data in the matrix based on the Start/End dates. I've tried adding a similar calculated Measure that determines whether the Start/End dates are in the filtered range, which is working properly - BUT when I then use it to filter the matrix, the filter is not working.

Example: filter is set to calculate only records that overlap with the dates 1/2/2025 - 1/10/2025. The IsInDateRange_Calc is configured the same as the Measure included above. But, if I filter on IsInDateRange_Calc = 1, the filter is not working as expected.

Record ID	Title_Calc	Start Date_Calc	End Date_Calc	IsInDateRange_Calc
Record A (here acting as parent)	Title A	1/1/2025	1/8/2025	1
(Record A expanded) --> Record B (child of Record A)	Title B	1/2/2025	1/3/2025	1 (correct, but not relevant since filter will be applied to parent-level)
(Record A expanded) --> Record C (child of Record A)	Title C	1/12/2025	1/20/2025	0 (correct, but not relevant since filter will be applied to parent-level)
Record B (here acting as parent)	Title B	1/2/2025	1/3/2025	1
Record C (here acting as parent)	Title C	1/12/2025	1/20/2025	0

Any insight or guidance on this is greatly appreciated!

[v-kathullac]

Thanks @lbendlin  for Addressing the issue.

Hi @mollycat ,

we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

Thanks.

[mollycat]

Unfortunately, given my requirements, I still have yet to find a solution.

[lbendlin]

Use EVALUATEANDLOG and the DAXDebugOutput app to troubleshoot your measure. Make sure that what you are trying to achieve is what your report users need and understand.

[mollycat]

I appreciate the reponse, but the measure is evaluating as expected, as indicated in the table. If I add the measure as a value in the matrix, the appropriate values are reflected. The issue arises when I try to apply the measure as a filter on the matrix.

[lbendlin]

Please provide sample data that covers your issue or question completely.
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[mollycat]

I am having some trouble linking my pbix file to a cloud storage location...is this sufficient?

I have recreated the issue using the following 3 tables:

Data Table:

Record ID	Title	Start Date	End Date
Record A	Title A	1/1/2025	1/8/2025
Record B	Title B	1/2/2025	1/3/2025
Record C	Title C	1/12/2025	1/20/2025

Relationships Table:

Parent	Child
Record A	Record B
Record A	Record C
Record B	null
Record C	null

The relationship is between the Data Table (Record ID) and the Relationships Table (Parent).

Calendar Table:

Calendar = CALENDAR(FIRSTDATE('Data Table'[Start Date]),LASTDATE('Data Table'[End Date]))

Measures for matrix values (to maintain child-level attributes when expanded, rather than inheriting from parent):

TITLE_CALC =

SWITCH(

    TRUE(),

    ISINSCOPE('Relationships Table'[Child]) && MAX('Relationships Table'[Child])<>BLANK(),

        CALCULATE(

            MAX('Data Table'[Title]),

            TREATAS(VALUES('Relationships Table'[Child]),'Data Table'[Record ID]),

            ALLCROSSFILTERED('Data Table')

        ),

    NOT ISINSCOPE('Relationships Table'[Child]),

        CALCULATE(

            MAX('Data Table'[Title]),

            TREATAS(VALUES('Relationships Table'[Parent]),'Data Table'[Record ID]),

            ALL('Relationships Table'[Parent])

    ))

START_DATE_CALC =

SWITCH(

    TRUE(),

    ISINSCOPE('Relationships Table'[Child]) && MAX('Relationships Table'[Child])<>BLANK(),

        CALCULATE(

            MAX('Data Table'[Start Date]),

            TREATAS(VALUES('Relationships Table'[Child]),'Data Table'[Record ID]),

            ALLCROSSFILTERED('Data Table')

        ),

    NOT ISINSCOPE('Relationships Table'[Child]),

        CALCULATE(

            MAX('Data Table'[Start Date]),

            TREATAS(VALUES('Relationships Table'[Parent]),'Data Table'[Record ID]),

            ALL('Relationships Table'[Parent])

    ))

END_DATE_CALC =

SWITCH(

    TRUE(),

    ISINSCOPE('Relationships Table'[Child]) && MAX('Relationships Table'[Child])<>BLANK(),

        CALCULATE(

            MAX('Data Table'[End Date]),

            TREATAS(VALUES('Relationships Table'[Child]),'Data Table'[Record ID]),

            ALLCROSSFILTERED('Data Table')

        ),

    NOT ISINSCOPE('Relationships Table'[Child]),

        CALCULATE(

            MAX('Data Table'[End Date]),

            TREATAS(VALUES('Relationships Table'[Parent]),'Data Table'[Record ID]),

            ALL('Relationships Table'[Parent])

    ))

Measure to determine whether the Start/End dates are in range based on the Date range selected by the user:

DateRangeCheck =

var _rangeStart = FIRSTDATE('Calendar'[Date])

var _rangeEnd = LASTDATE('Calendar'[Date])

RETURN

SWITCH(TRUE(),

    SELECTEDVALUE('Data Table'[Start Date]) <= _rangeEnd

    &&

    SELECTEDVALUE('Data Table'[End Date]) >= _rangeStart,"In range", "Not in range")

Measure to view the DateRangeCheck for parent and child records in the matrix:

DateRangeCheck_CALC =

SWITCH(

    TRUE(),

    ISINSCOPE('Relationships Table'[Child]) && MAX('Relationships Table'[Child])<>BLANK(),

        CALCULATE(

            [DateRangeCheck],

            TREATAS(VALUES('Relationships Table'[Child]),'Data Table'[Record ID]),

            ALLCROSSFILTERED('Data Table')

        ),

    NOT ISINSCOPE('Relationships Table'[Child]),

        CALCULATE(

            [DateRangeCheck],

            TREATAS(VALUES('Relationships Table'[Parent]),'Data Table'[Record ID]),

            ALL('Relationships Table'[Parent])

    ))

When the DateRangeCheck_CALC measure is added to the matrix, it is working perfectly. As the date range is changed via the Slicer, the values update to indicate "In range" or "Not in range" in real time. When I try to apply DateRangeCheck_CALC = "In range" as a matrix visual filter, it is not filtering properly.

Thank you for your consideration!

[lbendlin]

Your relationship table is slightly incorrect. Should be

That will then allow you to use the PATH functions if desired.

For the standard range check use INTERSECT or IN.

[mollycat]

Unfortunately, this is not working any differently when applied as a filter than the original measure. The left matrix shows the Days In Range measure, showing the correct values (no filter applied). The right matrix shows the matrix with a filter applied (Days In Range > 0 OR is not blank - I've tried both with the same result). Record A is showing as expected, Record B is not included.

[lbendlin]

works for me

Make sure the measure you use as a visual filter is not part of that visual.

[mollycat]

I really do appreciate the help on this...

I see in your file that the filter is working, but it is a bit different than my requirements. By changing the parent/child relationship, if I create a matrix in your attached pbix file, the implied hierarchy is not working as intended.

This is not the desired output:

Records B and C are standard records that have no children (or dependencies). They should be included in the list and then have no child records to expand.

Desired output:

Additionally, I see that in my sample file, the filter is working with this hierarchy if there are no other columns.

Working filter/matrix:

If the title is added, with no other changes, the filter stops working (Record B is removed):

[lbendlin]

Showing the same data twice in the same visual is a design red flag. I'm afraid I cannot support you with that.