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

  • selvakumarr's avatar
    selvakumarr
    Frequent Visitor
    3 years ago

    I have got 5411 (as expected) records with 5176 records 1s and rest as zero both as expected.
    It is happening the same with SUMX as well. The issue was when QMID is brought into dimension, then the individual or row level calcs are not matching the total.