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

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] )
    )))