Rankx Measure with Filter

[MichaelaMul]

Hello,

I am trying to adjust a current Ranking Measure to exclude certain Geographies from the Ranking.

This is the current measure:

Vol Sales Rank Geo = 
    RANKX (
        FILTER (
            ALLSELECTED('LatestSizes'), 
            NOT ( ISBLANK ( [Vol Sales]  ) )
            ),
        [Vol Sales] ,
        ,
        DESC,
        Dense
    )+0

I tried the following but for some reason it still includes the excluded. As you can see below, now the ranking starts at 3 instead of 1

Vol Sales Rank Geo = 
IF(sum(Geography_Table[New Sort])>12,
    RANKX (
        FILTER (
            ALLSELECTED('LatestSizes'), 
            NOT ( ISBLANK ( [Vol Sales]  ) )
            ),
        [Vol Sales] ,
        ,
        DESC,
        Dense
    )+0,0)

Any Suggestions?

Sample data with the desired ranking I want:

Geography	Time	Product	Volume Sales	New Sort	Desired Ranking
Total US - Multi Outlet+	Latest 4 Weeks Ending 03-16-25	Almond Milk	3,047,754	1
Total US - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	2,254,639	2
Total US - Drug	Latest 4 Weeks Ending 03-16-25	Almond Milk	16,004	3
Total New England w/ Albany - Multi Outlet+	Latest 4 Weeks Ending 03-16-25	Almond Milk	256,871	4
Total New England w/ Albany - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	218,495	5
Total New England w/out Albany - Multi Outlet+	Latest 4 Weeks Ending 03-16-25	Almond Milk	243,099	6
Total New England w/out Albany - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	208,636	7
Boston, MA - Multi Outlet+	Latest 4 Weeks Ending 03-16-25	Almond Milk	99,062	8
Boston, MA - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	86,229	9
Northern New England - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	45,022	10
Northern New England - Multi Outlet+	Latest 4 Weeks Ending 03-16-25	Almond Milk	53,896	11
New York, NY - Food	Latest 4 Weeks Ending 03-16-25	Almond Milk	221,194	12
Store 1	Latest 4 Weeks Ending 03-16-25	Almond Milk	278,315	13	3
Store 2	Latest 4 Weeks Ending 03-16-25	Almond Milk	82,175	14	6
Store 3	Latest 4 Weeks Ending 03-16-25	Almond Milk	25,464	15	10
Store 4	Latest 4 Weeks Ending 03-16-25	Almond Milk	255,172	16	4
Store 5	Latest 4 Weeks Ending 03-16-25	Almond Milk	12,221	17	17
Store 6	Latest 4 Weeks Ending 03-16-25	Almond Milk	60,238	18	7
Store 7	Latest 4 Weeks Ending 03-16-25	Almond Milk	6,929	19	20
Store 8	Latest 4 Weeks Ending 03-16-25	Almond Milk	10,293	21	18
Store 9	Latest 4 Weeks Ending 03-16-25	Almond Milk	15,462	22	14
Store 10	Latest 4 Weeks Ending 03-16-25	Almond Milk	6,989	23	19
Store 11	Latest 4 Weeks Ending 03-16-25	Almond Milk	30,435	24	9
Store 12	Latest 4 Weeks Ending 03-16-25	Almond Milk	16,842	25	13
Store 13	Latest 4 Weeks Ending 03-16-25	Almond Milk	304,199	26	1
Store 14	Latest 4 Weeks Ending 03-16-25	Almond Milk	303,098	27	2
Store 15	Latest 4 Weeks Ending 03-16-25	Almond Milk	5,178	28	21
Store 16	Latest 4 Weeks Ending 03-16-25	Almond Milk	22,198	29	11
Store 17	Latest 4 Weeks Ending 03-16-25	Almond Milk	4,648	30	22
Store 18	Latest 4 Weeks Ending 03-16-25	Almond Milk	13,807	31	16
Store 19	Latest 4 Weeks Ending 03-16-25	Almond Milk	35,465	32	8
Store 20	Latest 4 Weeks Ending 03-16-25	Almond Milk	14,754	33	15
Store 21	Latest 4 Weeks Ending 03-16-25	Almond Milk	245,274	34	5
Store 22	Latest 4 Weeks Ending 03-16-25	Almond Milk	17,258	35	12

Thank you!

[MichaelaMul]

Hi Prashanth, I tried to submit a support ticket but they don't support issues with DAX measures it seems. Do you know if there's a different group to reach out to?

[v-prasare]

Hi @MichaelaMul, as we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for your issue worked? or let us know if you need any further assistance here?

Thanks,

Prashanth Are

MS Fabric community support

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query

[MichaelaMul]

Hi! No, both solutions sadly didn't work. As you can see from my reply above. The 1st solution just made all of the rankings be 1 and then the 2nd solution had the same problem I was having, having the ranking start at 3.

[v-prasare]

Hi @MichaelaMul , 

In this scenario i suggest you to raise a support ticket here. so, that they can assit you in addressing the issue you are facing. please follow below link on how to raise a support ticket:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thanks,

Prashanth Are

[v-prasare]

Hi @MichaelaMul,

Thanks for reaching MS Fabric community support.

@Deku, Thanks for your promt response. additionally refer below

Vol_Sales_Rank_Geo = 
VAR FilterTable =
    FILTER (
        ALLSELECTED('LatestSizes'),
        NOT ISBLANK ( [Vol Sales] ) &&
        SUM(Geography_Table[New Sort]) > 12
    )
RETURN
    IF(
        SUM(Geography_Table[New Sort]) > 12,
        RANKX (
            FilteredTable,
            [Vol Sales],
            ,
            DESC,
            Dense
        ),
        BLANK() )

Thanks,

Prashanth Are

[MichaelaMul]

Hi!

Thank you both for your reply! I tried both and the first one just made every geography return "1" ranking. Then I tried the second one and I am getting the same problem of it starting the ranking at 3. 

[Deku]

Try iterating over just geography, and I assume vol sales does return blank, not 0

Vol Sales Rank Geo =
RANKX (
FILTER (
ALLSELECTED('LatestSizes'[geography]),
NOT ( ISBLANK ( [Vol Sales] ) )
),
[Vol Sales] ,
,
DESC,
Dense
)+0