Forum Discussion

J94's avatar
J94
Icon for Advocate II rankAdvocate II
1 month ago
Solved

Filter by Rank measure

Hi all,   I have a table in my report with Productline and MOdelNPIDesc and a measure Orders vs Forecast %. I only want to show the top 10 with the highest %. If I add the measure Top10  Orde...
Need Help
  • J94's avatar
    J94
    1 month ago

    Thank you danextian. This led me in the right direction. RemoveFilters was necessary for the measure. Because I already had a measure in the filter pane, the Ranking was like '7, 9, 10, 11, 13'. So it didn't start at 1 and sometimes it skipped rankings. Hence I had to make one small adjustment. This is the final working measure:

    Orders vs Forecast % TopN =
    VAR RankingTable =
        FILTER (
            CALCULATETABLE (
                ALLSELECTED (
                    'dim_product_attributes'[ProductLine],
                    'dim_product_attributes'[ModelNPIDesc]
                ),
                REMOVEFILTERS ( 'dim_product_attributes' )
            ),
            [Sales Forecast_Measure] > 50
        )
    RETURN
        RANKX (
            RankingTable,
            [Orders vs Forecast %],
            ,
            DESC,
            DENSE
        )


    As you can see, I added Filter and the [Sales Forecast_Measure] > 50 to the measure. Sales Forecast_Measure was already added in the filter pane, hence I needed to filter for it again in this measure (if I didn't include it, I had the same issue where I started with). Actually, this simplified measure is working for me as well:

    RANKX(
        FILTER(
            ALLSELECTED(
                dim_product_attributes[ProductLine],
                dim_product_attributes[ModelNPIDesc]
            ),
            [Sales Forecast_Measure] > 50
        ),
        [Orders vs Forecast %],
        ,
        DESC,
        DENSE
    )

     

J94's avatar
J94
Icon for Advocate II rankAdvocate II
1 month ago

johnt75  I did add the measures in the visual. But when I want to filter on the measures for the top 10 in the filter pane, its iteration context or something changes. This prevents me to see the actual top 10

johnt75's avatar
johnt75
Icon for Super User rankSuper User
1 month ago

How exactly are you applying the filter? I can see in your screenshot that the top visual doesn't have a filter on either Top 10 or TopN10EqualTo1.

Can you use Performance Analyzer to get the query generated for the visual without any filters, and then get the query when you apply a filter to either measure. Please also include a screenshot showing the visual and the filters.