To Help understand DAX MEASURE | RANKX | Filter Context | Row Context

[selvakumarr]

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?

[tamerj1 · Accepted solution]

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

    )))

[tamerj1]

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

[selvakumarr]

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]

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]

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]

@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]

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

[tamerj1]

@selvakumarr 
Ok, Let me simplify my question; What result are you expecting to see at the total level in this screenshot?

[selvakumarr]

@tamerj1 

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)

[tamerj1]

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

[selvakumarr]

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.

[tamerj1]

@selvakumarr 

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

[tamerj1]

@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]

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.

[Vegeta07]

Hi @selvakumarr 
Use calculate in the expression part or create a measure for that.

[selvakumarr]

hi @Vegeta07 , 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?

[Vegeta07]

Expression part of rankx

[selvakumarr]

I am sorry, how should I add an expression in place of column name in RANKX function.