performing filter and aggregate function on dataset before applying TOPN

[onefish]

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 fruit) and then use the TOPN function. This would be my approach using SQL anyway. I would appreciate any tips on how to complete this task in DAX. If it matters, I do a have a slicer referencing a column not displayed here, but I've managed to get my other visuals to be responsive to the slicer appropiately. Thank you!

Original Table 

Unique_ID	Status	Fruit
1	Available	Apple
2	Available	Apple
3	Available	Apple
4	Available	Apple
5	In-Review	Apple
6	Discontinued	Apple
7	In-Review	Apple
8	Available	Apple
9	Available	Apple
10	Available	Pear
11	Available	Pear
12	Available	Pear
13	Discontinued	Peach
14	Available	Peach
15	Available	Peach

Expected results:

Fruit	Count
Apple	6
Pear	3
Peach	2

[Juan-Power-bi · Accepted solution]

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.

[onefish]

Many thanks to both Juan-Power-Bi and cengizhanarslan, both of their solutions worked for me. 

[v-shchada-msft]

Hi @onefish,

Just checking in to see if your issue has been resolved. If you’re still facing any problems, please feel free to reach out and we’ll be happy to assist further.

Thank you.

[v-shchada-msft]

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.

[cengizhanarslan · Accepted solution]

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.

[Juan-Power-bi · Accepted solution]

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.