Why my measure cannot filter all combination in transition matrix?

[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()
)

This problem make me go crazy. I did not know where am I wrong. 😭

[v-pnaroju-msft · Accepted solution]

Hi danielsun,

Thank you for your inquiry through the Microsoft Fabric Community Forum.

Please find attached the screenshot and the PBIX file, which may assist in resolving the issue:

If you find our response helpful, kindly consider marking it as the accepted solution and providing kudos. This will help other community members who may be facing similar queries.

Should you have any further questions, please feel free to reach out to the Microsoft Fabric Community.

Thank you.

[danielsun]

Appreciate what your extra work. I will try, then come back to tell you the result later when I am available.

I am so curious what was wrong with my old DAX, how can your DAX work so well? May I know the reason? Casue I am just a beginner with Power BI.

[danielsun]

Really appreciate. That works.

BTW, the reason why I use dimension tables of from and  is to make sure that there still are row and column to show even if the fact table of rollrate do not have VOL PPM or DEFAULT. But I saw you also use that way and works perfectly. Thanks a lot, my BOSS. LOL