Forum Discussion
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 Ranked_risk ASC) as Rn
from FactPatient
WHERE QMID in (10,11,12,13)
and Year in (2022)
)
Select sum(Numerator) as Numerator -- A measure for Numerator
, Count(Memberid) as Denominator -- A measure for Denominator
from Members_max m
where m.rn = 1
Now what I am trying to do is
1. Get the Lowest Ranked Risk for each patient for the selected months (User can select One month, Multiple Months or without any filter on Date table.)
2. Fact Patient has One Entry for each member for all months with their ranked risk (Values like 1, 2,3, .etc.), Numerator will have 0 or 1.
Here is the DAX measure which is working as expected without any filters
VAR QM2_data =
FILTER (
FactPatient,
FactPatient[QMID] IN { 10, 11, 12, 13 }
)
VAR QM2_Dataset =
ADDCOLUMNS (
QM2_data,
"Rn", RANKX ( QM2_data, FactPatient[Ranked_Risk],, ASC, SKIP )
)
RETURN
SUMX( FILTER ( QM2_Dataset, [Rn] = 1 ) ,FactPatient[NUMERATOR] )
The measure is not working as expected when we apply any filter on Date table.
Data sample:
| Member ID | Ranked Risk | Numerator | Month | QMID |
1 | 2 | 0 | 2022-10-31 | 10 |
| 1 | 1 | 1 | 2022-09-30 | 11 |
| 1 | 3 | 1 | 2022-08-31 | 11 |
| 2 | 1 | 1 | 2022-09-30 | 10 |
| 2 | 2 | 0 | 2022-08-31 | 10 |
| 3 | 1 | 0 | 2022-08-31 | 11 |
| 3 | 2 | 0 | 2022-07-31 | 11 |
Case 1 :
When No filter applied, it is expected to pick the lowest ranked risk for each member that would be the below table
| MemberId | Ranked Risk | Numerator | Date | QMID |
| 1 | 1 | 1 | 2022-09-30 | 11 |
| 2 | 1 | 1 | 2022-09-30 | 10 |
| 3 | 1 | 0 | 2022-08-31 | 11 |
and the dax measure for numerator would yield a value of 2.
Issue part:
Now, if we select a month let's say August, 2022.
I was in an assumption that Numerator will be 1 after filter the dataset to Aug, 2022 and then do a ranking to arrive at following temp table tbale to do the sumX.
| Member ID | Ranked Risk | Numerator | Month | QMID |
| 1 | 3 | 1 | 2022-08-31 | 11 |
| 2 | 2 | 0 | 2022-08-31 | 10 |
| 3 | 1 | 0 | 2022-08-31 | 11 |
But Dax expression is not evaluated on top of current filter context(Aug,2022), instead the current filter context is only applied in the last step to filter the data(Bolded) after getting the Numerator value for each month i.e in our example
| Month | Numerator |
| Oct, 2022 | 1 |
| Sep, 2022 | 1 |
| Aug, 2022 | 0 |
Thus getting Zero as value rather than 1.
Summary:
I want this DAX expression to calculate the rank on top of current filter expression and do the some.
Could anyone help me get to understand what am I doing wrong?
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] ))))
18 Replies
- tamerj1Community Champion
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] )))) - AnonymousNot applicable
Hi selvakumarr
Use calculate in the expression part or create a measure for that.- AnonymousNot applicable
Expression part of rankx
- selvakumarrFrequent Visitor
I am sorry, how should I add an expression in place of column name in RANKX function.
- selvakumarrFrequent Visitor
hi Anonymous , I am getting an error when i use calculate on top on RANKX, saying 'A single value for a Column ranked risk cannot be found'.
Could you help me understand better?
- tamerj1Community Champion
Hi selvakumarr
You missing the context transition inside the ADDCOLUMNS function. You may try the following, however, this is not the optimum method to accomplish this result.
=
VAR QM2_Dataset =
ADDCOLUMNS (
QM2_data,
"Rn",
VAR QM2_data =
FILTER (
CALCULATETABLE ( FactPatient ),
FactPatient[QMID] IN { 10, 11, 12, 13 }
)
RETURN
RANKX ( QM2_data, FactPatient[Ranked_Risk],, ASC, SKIP )
)
RETURN
SUMX ( FILTER ( QM2_Dataset, [Rn] = 1 ), FactPatient[NUMERATOR] )- selvakumarrFrequent Visitor
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.- tamerj1Community Champion
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] ) )