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

Hi tamerj1 , Thanks for patiently replying to my queries and I am sorry that this solution still evaluates the whole expression for each QMID (Row level Context).
If I have to rephrase my query, There are two parts to this,
Until QM2_Dataset, I want to evaluate based on User applied filters, and only the final sum expression needs to be assessed in row context.
 I have tried to put the logic which I have in my mind in the screen shot below.


I want a Temp table expression evaluated for the current filter context and then apply the sum function on the temp table created.




tamerj1's avatar
tamerj1
Community Champion
3 years ago

selvakumarr 

I hope you understand that I don't see what you see. I don't have your data, data model or report to look at and fully understand the situation. I got the general Ideas but the issue is to get the correct results. Please try

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