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 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_IDStatusFruit
1AvailableApple
2AvailableApple
3AvailableApple
4AvailableApple
5In-ReviewApple
6DiscontinuedApple
7In-ReviewApple
8AvailableApple
9AvailableApple
10AvailablePear
11AvailablePear
12AvailablePear
13DiscontinuedPeach
14AvailablePeach
15AvailablePeach

 

Expected results:

FruitCount
Apple6
Pear3
Peach2

 

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

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

5 Replies

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

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

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

  • onefish's avatar
    onefish
    Regular Visitor

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