Forum Discussion

onefish's avatar
onefish
Regular Visitor
4 months ago
Solved

performing filter and aggregate function on dataset before applying TOPN

I have a table where information is logged individually. Before I use TOPN function, I need to filter the table for a condition (Status = "Available"), then do a group by (Sum count of unique ID by f...
  • Juan-Power-bi's avatar
    4 months ago

    This is actually pretty straightforward in DAX. You just need a measure that filters for "Available" before counting, then use that measure in a visual with Fruit on the rows and a Top N visual-level filter applied.
    First, create this measure:
    daxAvailable Count =
    CALCULATE(
    DISTINCTCOUNT('Table'[Unique_ID]),
    'Table'[Status] = "Available"
    )
    Then put Fruit in your visual's rows and drop this measure in as the value. The grouping happens automatically since Fruit is in the visual context.
    For the Top N part, add a visual-level filter on Fruit, set it to "Top N" by your Available Count measure, and set N to however many you want. That gives you exactly the SQL equivalent of filter → group by → TOPN.
    Your slicer should also work fine with this since CALCULATE respects the existing filter context from slicers.

  • cengizhanarslan's avatar
    4 months ago

    Please try the measure below:

    Available Count =
    CALCULATE (
        DISTINCTCOUNT ( Table1[Unique_ID] ),
        Table1[Status] = "Available"
    )

     

    Place Fruit in the Rows field of a table visual and this measure as the value. Then apply TOPN by adding a visual-level Top N filter: Filters on this visual → Available Count → Filter type: Top N → Show items: Top 3 → By value: Available Count → Apply filter.