DAX showing various results

[NandinAna]

Hi ,

I am finding problem with the results produced by below dax,one shows the correct results and other shows the wrong result and the only differnce here is All and Allselected,rest of the query is same.Please could someone help on this where i might be going wrong.

Which results in correct ranking data

.RankMedian_TimeToSell = IF([.Count_Sales_ALL] < 10, "-", RANKX(ALLSELECTED(IMPORT_SQL_monthly[MemberFirm]),[.Median_TimeToSell_ALL],,ASC))

.Median_TimeToSell_ALL = CALCULATE(MEDIAN(IMPORT_SQL_monthly[TimeToSell]),OR(IMPORT_SQL_monthly[CurrentStatus]="Sold",IMPORT_SQL_monthly[CurrentStatus]="SaleComplete"), USERELATIONSHIP('TBL_CALENDAR_date'[Date],IMPORT_SQL_monthly[DateSold]), ALL(IMPORT_SQL_monthly[OrganisationId]), USERELATIONSHIP(IMPORT_SQL_monthly[SellingPrice_bin_25K_label], TBL_SLICE_Price[Axis]))

results in wrong ranking data

. Median_FIRM_TimeToSell_ALL_RANK =

var firm_userprincipalname = [MF_USERPRINCIPALNAME]

RETURN

IF([.Sum_FIRM_Sales_ALL] >= [.val_Rank_Threshold],

CALCULATE(

    RANKX (

        ALL ( IMPORT_SQL_monthly[MemberFirm] ),

        CALCULATE ( MEDIAN( IMPORT_SQL_monthly[TimeToSell] ) ),

        CALCULATE ( MEDIAN( IMPORT_SQL_monthly[TimeToSell] ), LOOKUP_MF_USERS[PrincipalName] = firm_userprincipalname )

        ,ASC),

    OR(IMPORT_SQL_monthly[CurrentStatus]="Sold", IMPORT_SQL_monthly[CurrentStatus]="SaleComplete"),

    USERELATIONSHIP( IMPORT_SQL_monthly[DateSold], TBL_CALENDAR_date[Date]),

    USERELATIONSHIP( IMPORT_SQL_monthly[MemberFirm], LOOKUP_MF_USERS[firm])

)

, "-" )

[Sahir_Maharaj]

Hello @NandinAna,

The difference between the two DAX formulas is that the first one uses ALLSELECTED function, while the second one uses ALL function. ALL function removes all filters from the specified column or table, while ALLSELECTED function removes all filters except the ones that come from the visual context.

Therefore, the reason why the results are different could be due to the different set of filters being applied to the table in each case.

To further investigate the issue, you could try comparing the filter context of the two measures by adding a table or matrix visual to your report, with the MemberFirm column and the two measures. This would show you the value of each measure for each MemberFirm, along with the filters applied to the visual.

Another thing to check is whether the relationship between the tables is correctly set up and whether the filters are being propagated as expected.

Hope this helps!