Forum Discussion
performing filter and aggregate function on dataset before applying TOPN
- 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. - 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.
Hi onefish,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to the cengizhanarslan for sharing valuable insights.
Could you please confirm if your issue has been resolved using the suggested approach? This will help other community members facing similar scenarios.
Thank you.