Getting the value of a measure to use in a filter

[Mi_80]

Hi 

I have a measure that can rank my data below:  I need to be able to then get other values that correspond to the record that has this rank but icannot seem to 'grab' this rank to use in a lookup of filter.  Any help please:

Rank = RANKX(CALCULATETABLE(SUMMARIZE(table,table[account],"total",SUM(table[Amount])),Filter(ALLSELECTED(Table),table[statuscodename]="Active" && [property]="Yes" &&  table[Region]=SELECTEDVALUE(table[Region]))),[total],,ASC,Skip)

Or can I turn this into a column but I need to be able to filter with slicer preferably

Thanks

[Anonymous]

Hi @Mi_80 ,

If your measure works, it is suggested that you can directly add this measure into a filter. Please refer to

Add a filter to a report in Power BI - Power BI | Microsoft Learn

If you want a calculated column, please try

Rank =
RANKX (
    CALCULATETABLE (
        SUMMARIZE ( table, table[account], "total", SUM ( table[Amount] ) ),
        FILTER (
            Table,
            table[statuscodename] = "Active"
                && [property] = "Yes"
                && table[Region] = EARLIER ( table[Region] )
        )
    ),
    [total],
    ,
    ASC,
    SKIP
)

Because of the influence of some context, and the difference in the nature of the measure and calculated columns, it is possible to run incorrectly.

Please provide more details, including your data model. It would be nice to be able to provide some virtual sample data.

Best Regards,
Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly