Forum Discussion
To Help understand DAX MEASURE | RANKX | Filter Context | Row Context
Please try
M1 =SUMX(VALUES(FactMemberQualityMeasures[QMID]),VAR CurrentQMID =FactMemberQualityMeasures[QMID]RETURNSUMX (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])RETURNIF(CurrentQMID = maxQMID,SUMX ( QM2_Dataset, FactMemberQualityMeasures[DENOMINATOR] ))))
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] )
)
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.
selvakumarr
Ok, Let me simplify my question; What result are you expecting to see at the total level in this screenshot?QMID | Q Num Current | Q Num Expected 10 5195 4924 11 36 35 12 125 123 13 94 94 Total 5176 5176 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)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] ) )