Is there a way to filter by top 10 for only some field parameters?

[cb16]

I have a graph that is populated by 2 field parameters, one for the x-axis, and one for the y-axis. The x-axis parameter has 6 possible options. 2/6 of those have an unruly number of categories, so I am wondering if there is a way to filter by top 10 only for those 2 categories from the x-axis field parameter. The other 4 should display all categories.

I have created a measure that returns the selected y-axis value. I can filter top 10 on an individual x-axis option by that measure and it works. But if I switch to a different x-axis option, it keeps the filter as the original x-axis option.

Another way to explain what is happening when I try to do this:

X Field Parameter: A, B, C, D, E, F

Want only options A and B filtered by top 10

Select A, filter by top 10 by 'selected Y-Axis' measure

Select B, filtered by A top 10 when it should be B top 10

If I have to scrap this set up and approach a different way that's fine too.

[kushanNa · Accepted solution]

Hi @cb16 

I'm not sure if there's an easy solution for this , but here is the solution i came up with 

Create a calculated table

CombinedAxis =
UNION (
    SELECTCOLUMNS('SalesData', "AxisType", "Product", "AxisValue", 'SalesData'[Product]),
    SELECTCOLUMNS('SalesData', "AxisType", "Region", "AxisValue", 'SalesData'[Region]),
    SELECTCOLUMNS('SalesData', "AxisType", "Category", "AxisValue", 'SalesData'[Category])
)

Create a new table for y-axis selection 

MetricSelector = DATATABLE("Metric", STRING, {{"Sales"}, {"Profit"}})

create a measure 

SelectedMetric = SELECTEDVALUE(MetricSelector[Metric], "Sales")

create another measure 

Dynamic Y-Axis Value =
VAR AxisType = SELECTEDVALUE(CombinedAxis[AxisType])
VAR AxisValue = SELECTEDVALUE(CombinedAxis[AxisValue])
VAR Metric = [SelectedMetric]

RETURN
    SWITCH(
        TRUE(),
        Metric = "Sales" && AxisType = "Product", CALCULATE(SUM(SalesData[Sales]), SalesData[Product] = AxisValue),
        Metric = "Sales" && AxisType = "Region",  CALCULATE(SUM(SalesData[Sales]), SalesData[Region] = AxisValue),
        Metric = "Sales" && AxisType = "Category",CALCULATE(SUM(SalesData[Sales]), SalesData[Category] = AxisValue),
        Metric = "Profit" && AxisType = "Product", CALCULATE(SUM(SalesData[Profit]), SalesData[Product] = AxisValue),
        Metric = "Profit" && AxisType = "Region",  CALCULATE(SUM(SalesData[Profit]), SalesData[Region] = AxisValue),
        Metric = "Profit" && AxisType = "Category",CALCULATE(SUM(SalesData[Profit]), SalesData[Category] = AxisValue),
        BLANK()
    )

create a measure to pick top N on specific column i'm using product column in my example Top N 1 , you can make 10 in your example and your column name

Show Row3 = 
VAR AxisType = SELECTEDVALUE(CombinedAxis[AxisType])
VAR AxisValue = SELECTEDVALUE(CombinedAxis[AxisValue])
VAR Metric = [SelectedMetric]

VAR Top2 =
    TOPN(
        1, -- change top N as you wish 
        SUMMARIZE(
            SalesData,
            SalesData[Product],
            "Val", SWITCH(Metric, "Sales", SUM(SalesData[Sales]), "Profit", SUM(SalesData[Profit]))
        ),
        [Val],
        DESC
    )

VAR IsInTop2 = AxisValue IN SELECTCOLUMNS(Top2, "Product", SalesData[Product])

RETURN
    IF(AxisType = "Product", IF(IsInTop2, 1, 0), 1)

drag the above measure and put it in the chart visual and show row3 = 1 , is 1 mean like activate this filter in here not top n 1 

Final Visual Setup

• Slicer 1: CombinedAxis[AxisType] (Product / Region / Category)

• Slicer 2: MetricSelector[Metric] (Sales / Profit)

• Bar Chart:

  • X-axis: CombinedAxis[AxisValue]

  • Y-axis: Dynamic Y-Axis Value

  • Filter: Show Row3 = 1

once you set it up you can see when i pick product i can see only top 1 even thought there are 4 products , but when i select some thing like region i see all , i belive this is what you need ? 

[danextian]

Hi @cb16 

Field parameters are essentially individual columns or measures. Therefore, any filtering logic in your measure must account for changes in the selected parameter. The only column from the field parameter table that can be directly referenced is the "Order" column.

Below is the measure used in the GIF. The rank is used as a visual filter.

Rank by Revenue = 
VAR _ParameterOrder =
    SELECTEDVALUE ( FieldParameter[FieldParameter Order] )
VAR _CategoryRank =
    RANKX ( ALL ( Category ), [Total Revenue],, DESC, DENSE )
VAR _GeoRank =
    RANKX ( ALL ( Geo ), [Total Revenue],, DESC, DENSE )
RETURN
    SWITCH ( _ParameterOrder, 0, _CategoryRank, 1, _GeoRank )

[Akash_Varuna]

Hi @cb16  Create a DAX measure that ranks categories dynamically, applying the top 10 filter only for selected X-axis options (A and B). Use the measure as a visual filter, returning 1 for top 10 in A/B and all categories for others. Ensure the logic adapts based on the selected X-axis parameter in the field or another way is to use pre-processed tables for A/B’s top 10 to improve performance.