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 the quick reply, this is giving me correct result for one month, but if I select multiple months the numbers are not as expected because the measure is evaluating Rank for each month individually and then sums up at the end and it is not what I wanted. 
If multiple months are selected I want to pick only one record per patient with the lowest ranked_risk and then do the sum of numerator.

tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi selvakumarr 
In this case please try

=
SUMX (
    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
        SUMX ( QM2_Dataset, FactPatient[NUMERATOR] )
)
  • 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.

    • 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.