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 R...
"FILTER"
"RANKX"
DAX FILTER
DAX ROW CONTEXT
  • tamerj1's avatar
    tamerj1
    3 years ago

    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] )
        )))
tamerj1's avatar
tamerj1
Community Champion
3 years ago

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
3 years ago

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
    3 years ago

    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] )
)
    • selvakumarr's avatar
      selvakumarr
      Frequent Visitor
      3 years ago

      Hi tamerj1 , Sorry, I was just over the moon when I saw the Numbers are matching for each month, Multiple months and all months (Data is matching for all Scenarios), but When I drill down to QMID level, the sum of Individual numbers are higher than Total like below 

      I could not debud betwwen this row_context and filter context. Could you me understand why is this happening?

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        selvakumarr 

        Try what you get out of this. It should give better idea. Deactivate total before doing that

        =
        CONCATENATEX (
        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
        CONCATENATEX ( QM2_Dataset, FactPatient[NUMERATOR], " - " ),
        UNICHAR ( 10 )
        )