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