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 

I was going through your reply once again and realized that I misunderstood your concern in the first read. 
The measure calculates based on the minimum Ranked_Risk of each Member ID within the selected period. The period at the total level is basically all dates as there is no filter context. You will get the same value at the total level (or at a card visual) regardless of which date attribute you slice by. 
You may have noticed already that sum the individual values at month level it differs from that at quarter level than week than year. In other words this measure is non-additive. 
If you want to force additivity the you have to decide at which level. For example to force additivity at QMID level we can do

=
SUMX (
SUMMARIZE ( FactPatient, FactPatient[Member ID], FactPatient[QMID] ),
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

tamerj1 ,  I am so sorry that I am unable to convey my query here in one go and going on an loop, what I am trying to achieve using this DAX measure is

  • Step 1: To get one record per Patient with the lowest Risk Rank for the current filter selection (Month filter or any other filter) into a table variable. 
  • Step 2: Sum the Numerators stored in the table variable. 

This way I thought I will be able to report, sum of numerator for each category in the filtered context. 

But the formula is executed with Row context for both step 1 and carried forwarded to step2 I guess, If it is so how can I make the row context applied only to the step2, and step 1 executed on filter context alone?
As per your suggestion if I add QMID to Summarizecolumn expression, for each patient and QMIDs the TOPN function is being evaluated which is not what I intended to do.