Forum Discussion

selvakumarr's avatar
selvakumarr
Frequent Visitor
3 years ago
Solved

To Help understand DAX MEASURE | RANKX | Filter Context | Row Context

I am not sure how to debug or search this below issue.
My Requirement in SQL :

With Members_max as (
SELECT MemberID
,Ranked_risk
,Numerator
,row_number() over (partition by memberid order by Ranked_risk ASC) as Rn
from FactPatient
WHERE QMID in (10,11,12,13)
and Year in (2022)
)

Select sum(Numerator) as Numerator -- A measure for Numerator
, Count(Memberid) as Denominator -- A measure for Denominator
from Members_max m
where m.rn = 1

 Now what I am trying to do is 
1. Get the Lowest Ranked Risk for each patient for the selected months (User can select One month, Multiple Months or without any filter on Date table.)

2. Fact Patient has One Entry for each member for all months with their ranked risk (Values like 1, 2,3, .etc.), Numerator will have 0 or 1.

 

Here is the DAX measure which is working as expected without any filters
    

VAR QM2_data =
        FILTER (
            FactPatient,
            FactPatient[QMID] IN { 10, 11, 12, 13 }
        )

VAR QM2_Dataset =
        ADDCOLUMNS (
            QM2_data,
            "Rn", RANKX ( QM2_data, FactPatient[Ranked_Risk],, ASC, SKIP )
        )
RETURN
SUMX( FILTER ( QM2_Dataset, [Rn] = 1 ) ,FactPatient[NUMERATOR] )

The measure is not working as expected when we apply any filter on Date table.

Data sample:

Member IDRanked RiskNumeratorMonthQMID

1

202022-10-3110
1112022-09-3011
1312022-08-3111
2112022-09-3010
2202022-08-3110
3102022-08-3111
3202022-07-3111

 

Case 1 :

When No filter applied, it is expected to pick the lowest ranked risk for each member that would be the below table 

MemberIdRanked RiskNumeratorDateQMID
1112022-09-3011
2112022-09-3010
3102022-08-3111

and the dax measure for numerator would yield a value of 2.

Issue part:

Now, if we select a month let's say August, 2022.
I was in an assumption that Numerator will be 1 after filter the dataset to Aug, 2022 and then do a ranking to arrive at following temp table tbale to do the sumX.

Member IDRanked RiskNumeratorMonthQMID
1312022-08-3111
2202022-08-3110
3102022-08-3111

 

But Dax expression is not evaluated on top of current filter context(Aug,2022), instead the current filter context is only applied in the last step to filter the data(Bolded) after getting the Numerator value for each month i.e in our example

MonthNumerator
Oct, 20221
Sep, 2022

1

Aug, 2022

0

Thus getting Zero as value rather than 1.

Summary:

I want this DAX expression to calculate the rank on top of current filter expression and do the some.
Could anyone help me get to understand what am I doing wrong?

 

  • selvakumarr 

    Please try

    M1 =
    SUMX(
        VALUES(FactMemberQualityMeasures[QMID]),
        VAR CurrentQMID =
            FactMemberQualityMeasures[QMID]
        RETURN
        SUMX (
            CALCULATETABLE(VALUES ( FactMemberQualityMeasures[MemberID] )),
            VAR QM2_data =
                CALCULATETABLE(
                    FactMemberQualityMeasures,
                    FactMemberQualityMeasures[QMID] IN { 10, 11, 12, 13 },
                    ALL ( DimQualityMeasures[QMID])
                )
            VAR QM2_Dataset =
                TOPN ( 1, QM2_data, FactMemberQualityMeasures[RISK_RANK], ASC )
            VAR maxQMID =
                MAXX(QM2_Dataset, FactMemberQualityMeasures[QMID])
            RETURN
                IF(
                    CurrentQMID = maxQMID,
                    SUMX ( QM2_Dataset, FactMemberQualityMeasures[DENOMINATOR] )
        )))

18 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion

    selvakumarr 

    Please try

    M1 =
    SUMX(
        VALUES(FactMemberQualityMeasures[QMID]),
        VAR CurrentQMID =
            FactMemberQualityMeasures[QMID]
        RETURN
        SUMX (
            CALCULATETABLE(VALUES ( FactMemberQualityMeasures[MemberID] )),
            VAR QM2_data =
                CALCULATETABLE(
                    FactMemberQualityMeasures,
                    FactMemberQualityMeasures[QMID] IN { 10, 11, 12, 13 },
                    ALL ( DimQualityMeasures[QMID])
                )
            VAR QM2_Dataset =
                TOPN ( 1, QM2_data, FactMemberQualityMeasures[RISK_RANK], ASC )
            VAR maxQMID =
                MAXX(QM2_Dataset, FactMemberQualityMeasures[QMID])
            RETURN
                IF(
                    CurrentQMID = maxQMID,
                    SUMX ( QM2_Dataset, FactMemberQualityMeasures[DENOMINATOR] )
        )))
  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi selvakumarr 
    Use calculate in the expression part or create a measure for that.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Expression part of rankx

      • selvakumarr's avatar
        selvakumarr
        Frequent Visitor

        I am sorry, how should I add an expression in place of column name in RANKX function.

    • selvakumarr's avatar
      selvakumarr
      Frequent Visitor

      hi Anonymous , I am getting an error when i use calculate on top on RANKX, saying 'A single value for a Column ranked risk cannot be found'.


      Could you help me understand better?

  • tamerj1's avatar
    tamerj1
    Community Champion

    Hi selvakumarr 

    You missing the context transition inside the ADDCOLUMNS function. You may try the following, however, this is not the optimum method to accomplish this result. 
    =
    VAR QM2_Dataset =
    ADDCOLUMNS (
    QM2_data,
    "Rn",
    VAR QM2_data =
    FILTER (
    CALCULATETABLE ( FactPatient ),
    FactPatient[QMID] IN { 10, 11, 12, 13 }
    )
    RETURN
    RANKX ( QM2_data, FactPatient[Ranked_Risk],, ASC, SKIP )
    )
    RETURN
    SUMX ( FILTER ( QM2_Dataset, [Rn] = 1 ), FactPatient[NUMERATOR] )

    • selvakumarr's avatar
      selvakumarr
      Frequent Visitor

      hi tamerj1 , Thanks for the quick reply, this is giving me correct result for one month, but if I select multiple months the numbers are not as expected because the measure is evaluating Rank for each month individually and then sums up at the end and it is not what I wanted. 
      If multiple months are selected I want to pick only one record per patient with the lowest ranked_risk and then do the sum of numerator.

      • tamerj1's avatar
        tamerj1
        Community Champion

        Hi selvakumarr 
        In this case please try

        =
        SUMX (
            VALUES ( FactPatient[Member ID] ),
            VAR QM2_data =
                FILTER (
                    CALCULATETABLE ( FactPatient ),
                    FactPatient[QMID] IN { 10, 11, 12, 13 }
                )
            VAR QM2_Dataset =
                TOPN ( 1, QM2_data, FactPatient[Ranked_Risk], ASC )
            RETURN
                SUMX ( QM2_Dataset, FactPatient[NUMERATOR] )
        )