Why can't my matrix show all values?

[danielsun]

Hi all,

I have a matrix of Average Roll Rate (bottom left), there should be a value in C to D60 cell. But it is empty.

Related DAX measures:

Average Roll Rate = 
VAR PeriodM  = SELECTEDVALUE( slt_period[Months], 3 )
VAR MaxDate  = MAX( rollrate[asofdate] )

VAR DateRange =
    CALCULATETABLE(
        DATESINPERIOD(
            rollrate[asofdate],
            MaxDate,
            - PeriodM,
            MONTH
        ),
        ALL( rollrate[asofdate] )
    )

VAR Numerator =
    CALCULATE(
        [Selected Roll Measure],
        DateRange
    )

VAR Denominator =
    CALCULATE(
        [Base Roll Measure],
        DateRange
    )

RETURN
DIVIDE( Numerator, Denominator )

slt_period = 
DATATABLE(
  "Months", INTEGER,
  {
    { 3 },
    { 6 },
    { 12 }
  }
)

Selected Roll Measure = 
SWITCH(
    SELECTEDVALUE( slt_rollrate[Metric], "UPB" ),
    "OBS", [OBS],
    "UPB", [UPB]
)

UPB = 
    VAR FromStatus = SELECTEDVALUE(rollrate[delinq], "ALL")
    VAR ToStatus = SELECTEDVALUE(rollrate[next_delinq], "ALL")
    VAR Result =
        SUMX(
            FILTER(
                ALLSELECTED(rollrate),
                (rollrate[delinq] =
                    FromStatus && rollrate[next_delinq] = ToStatus
                    || FromStatus = "ALL" && rollrate[next_delinq] = ToStatus
                    || ToStatus = "ALL" && rollrate[delinq] = FromStatus
                )
            ),
            rollrate[balance]
        )
    RETURN
        IF(
            HASONEVALUE(rollrate[delinq]) && HASONEVALUE(rollrate[next_delinq]),
            Result,
            SUMX(
                FILTER(
                    ALLSELECTED(rollrate),
                    (FromStatus =
                        "ALL" && rollrate[next_delinq] = ToStatus
                        || ToStatus = "ALL" && rollrate[delinq] = FromStatus
                    )
                ),
                rollrate[balance]
            )
        )

OBS = 
    VAR FromStatus = SELECTEDVALUE(rollrate[delinq], "ALL")
    VAR ToStatus = SELECTEDVALUE(rollrate[next_delinq], "ALL")
    VAR Result =
        COUNTX(
            FILTER(
                ALLSELECTED(rollrate),
                (rollrate[delinq] =
                    FromStatus && rollrate[next_delinq] = ToStatus
                    || FromStatus = "ALL" && rollrate[next_delinq] = ToStatus
                    || ToStatus = "ALL" && rollrate[delinq] = FromStatus
                )
            ),
            rollrate[loanid]
        )
    RETURN
        IF(
            HASONEVALUE(rollrate[delinq]) && HASONEVALUE(rollrate[next_delinq]),
            Result,
            COUNTX(
                FILTER(
                    ALLSELECTED(rollrate),
                    (FromStatus =
                        "ALL" && rollrate[next_delinq] = ToStatus
                        || ToStatus = "ALL" && rollrate[delinq] = FromStatus
                    )
                ),
                rollrate[loanid]
            )
        )

Base Roll Measure = 
VAR FromStatus = SELECTEDVALUE( rollrate[delinq], "ALL" )
RETURN
SWITCH(
    SELECTEDVALUE( slt_rollrate[Metric], "UPB" ),
    "UPB",
    CALCULATE(
        SUM( rollrate[balance] ),
        FILTER(
            ALLSELECTED( rollrate ),
            rollrate[delinq] = FromStatus
            || FromStatus = "ALL"
        )
    ),

    "OBS",
    CALCULATE(
        COUNTROWS(
            FILTER(
                ALLSELECTED( rollrate ),
                rollrate[delinq] = FromStatus
                || FromStatus = "ALL"
            )
        )
    )
)

Here is my relationship:

from[prevdelinq] -> rollrate[delinq]

to[delinq] -> rollrate[next_delinq]

row Field in the Matrix is filled by from[prevdelinq]

column Field in the Matrix is filled by to[delinq]

value Field in the Matrix is filled by measure of Average Roll Rate

As you can see, there are some outer slicers: asofdate, trust, pool, deal, servicer and period to choose.
For Average Roll Rate, the algorithm is SUM(balance when delinq = "C" and next_delinq = "D60") / SUM(balance when delinq = "C").
In my data source, I checked C to D60 indeed has a ratio:

select
    sum(balance)
from ptan.rollrate
where asofdate between '20240531' and '20250430'
and delinq = 'C'
and next_delinq = 'D60';
-- 2129785.8699999996

select
    sum(balance)
from ptan.rollrate
where asofdate between '20240531' and '20250430'
  and delinq = 'C';
-- 8601604221.530008

select 2129785.8699999996 / 8601604221.530008 * 100;
-- 0.0247603332488700

But when I choose Date as 4/30/2025 and Period is 12, there is no C to D60 in the matrix.

The weird thing is when I choose multiple dates in Date slicer from 2024/05/31 to 2025/04/30. The Roll Rate Matrix can show the correct result:

The Roll Rate matrix's value Field is just filled by this Measure:

Selected Value = 
SWITCH(
    SELECTEDVALUE( slt_rollrate[Metric] ),
    "UPB", msr_rollrate[UPB],
    "OBS", msr_rollrate[OBS],
    BLANK()
)

Can somone help me please? Thanks

[v-hashadapu]

Hi @danielsun , Thank you for reaching out to the Microsoft Community Forum.

This is because the asofdate slicer filters the rollrate table down to that one date before your 12-month date range can apply. This prevents your measure from seeing any data outside of that date, even though your SQL confirms the transition exists. You need to remove the slicer's effect on asofdate while keeping all other slicers and the matrix filters intact. Try below:

Average Roll Rate =

VAR PeriodM = SELECTEDVALUE(slt_period[Months], 3)

VAR MaxDate = MAX(rollrate[asofdate])

VAR DateRange =

    DATESINPERIOD(rollrate[asofdate], MaxDate, -PeriodM, MONTH)

VAR Numerator =

    CALCULATE(

        [Selected Roll Measure],

        KEEPFILTERS(DateRange),

        ALLSELECTED(rollrate)

    )

VAR Denominator =

    CALCULATE(

        [Base Roll Measure],

        KEEPFILTERS(DateRange),

        ALLSELECTED(rollrate)

    )

RETURN

DIVIDE(Numerator, Denominator)

If needed, test it using debug measures to confirm it matches your SQL numbers. Example:

Numerator Debug =

CALCULATE([Selected Roll Measure], KEEPFILTERS(DATESINPERIOD(rollrate[asofdate], MAX(rollrate[asofdate]), -12, MONTH)), ALLSELECTED(rollrate))

Denominator Debug =

CALCULATE([Base Roll Measure], KEEPFILTERS(DATESINPERIOD(rollrate[asofdate], MAX(rollrate[asofdate]), -12, MONTH)), ALLSELECTED(rollrate))

For large models, consider using a separate calendar table to handle date filtering more efficiently.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[danielsun]

Hello @v-hashadapu 

Thanks for you reply.

I tried your DAX, but currently the matrix cells are all empty as below:

Also, I selected your two debugs in one Table Visual above FYI.

In my guess, I think maybe the Date slicer impact the row of DELINQ and the column of NEXT_DELINQ to fetch. Because I can only see C to D60 has values when I choose Date: 5/31/2024. That is saying that on 04/30/2025, there is Delinq of C but no Next_delinq of D60 so that this cell under 04/30/2025 is empty. But I prefer this matrix is calculated by SUM_C_to_D60(before 3/6/12 months to the selected date in Date slicer) / SUM_C (before 3/6/12 months to the selected to the selected date in Date Slicer)

If you need more information from me, please let me know. I really appreciate what you have done.

[v-hashadapu]

Hi @danielsun , Thank you for reaching out to the Microsoft Community Forum.

Can you confirm if the slicer or model filters are indirectly filtering the from[prevdelinq] or to[delinq] tables? That might prevent certain transitions like C -> D60 from even appearing in the matrix when only 1 date is selected.

As a test, can you try building the matrix using rollrate[delinq] and rollrate[next_delinq] directly or use a combined Delinquency table that’s not filtered by relationships?

[danielsun]

Hi @v-hashadapu ,

I tryied as you said, disconnected table of rollrate with tables of "from" and "to". Then use your DAX, and change row and column Field in Matrix to delinq and next_delinq columns in rollrate table. But things happened below:

[v-hashadapu · Accepted solution]

Hi @danielsun ,Thanks, this confirms that the matrix setup is no longer the issue. Can you try the following just to isolate the problem:

1. Create a simple card or table visual that shows:

CALCULATE(COUNTROWS(rollrate), KEEPFILTERS(DateRange), ALLSELECTED(rollrate))

where DateRange is:

DATESINPERIOD(rollrate[asofdate], MAX(rollrate[asofdate]), -12, MONTH)

2. Then another one that counts rows with:

rollrate[delinq] = "C" && rollrate[next_delinq] = "D60"

using below measure:

C_to_D60_Count =
CALCULATE(
COUNTROWS(rollrate),
rollrate[delinq] = "C",
rollrate[next_delinq] = "D60",
KEEPFILTERS(DATESINPERIOD(rollrate[asofdate], MAX(rollrate[asofdate]), -12, MONTH)),
ALLSELECTED(rollrate)
)

This will confirm whether the data is being filtered out entirely, even with the correct DAX.