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.
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.