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 
Ok, Let me simplify my question; What result are you expecting to see at the total level in this screenshot?

selvakumarr's avatar
selvakumarr
Frequent Visitor
3 years ago

tamerj1 

QMID | Q Num Current | Q Num Expected
1051954924
113635
12125123
139494
Total51765176

 

If you look the value for QM 10 current implementation, it says 5195 which is greater than the total and why it is greater than Total because the expression is being evaluated for QM 10, taking the QM10 item and finding the lowest risk in that population to sum the numerator. For each reporting row the TOPN population changed with respect to current row context.
I want to calculate total at selected Months level and then split that number for each category like it is split in Q Num Expected column. 
In a nutshell:
Total should match to sum of Unique patient's Numerator within selected time frame. (Which is matching now)
Sum of Individual rows should match to Total. (It is not happening)

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    selvakumarr 
    Now I got you.

    Please try

    =
SUMX (
    VALUES ( FactPatient[Member ID] ),
    VAR QM2_data =
        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 , 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.