Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

Summarise counts of counts

I have a table containing a list of 'internals' per customer. We need to know how many customers we have for each count of internals (which will be displayed in a chart with bins).


For example:


1 internal - 2000 customers

2-5 internals - 5000 customers

6-20 internals - 2000 customers

21-100 internals - 100 customer


I cant figure out how to do this. Note my dataset is running in DirectQuery mode, which limits what I can do severly.


I can produce the table of results I need in SQL easily with the following query:


COUNT(t.internalcount) AS countsum
COUNT([int_id]) AS internalcount
FROM [internal]
GROUP BY cust_id
) AS t
GROUP BY t.internalcount


I could put this in a SQL view and import in to Power BI, but that would prevent a lot of Power BI function working like slicers etc.


Any help would be appreciated.

Solution Supplier
Solution Supplier

Do you need a DAX query, or are you trying to do this through a measure and some attributes?

To be honest, I dont know - I just need a graph like this:



Hi @bn8959,


According to your description, a segment table may be needed in this scenario. And I don't think it's a bad choice to use a segment table here.Smiley Happy



Helper I
Helper I

The concept you are trying to do is "dynamic segmentation". Please refer to this excellent article.

Thanks for the reply - this is exactly right, and I had found that. However, that is not possible in DirectQuery mode, as it requires creation of segment tables.

Thanks for letting me know. I just started on same so I gave you the link. Wondering how direct query limits this as long as you can create a new table? Just trying to understand and also get more information.

I havent looked in too much detail yet as I fell at the first hurdle (creating the segment table). I guess I could create the table in SQL and import with DirectQuery, but it seems to be getting messy. Ideally I wanted to use the Group Bin feature anyway (and not have a segment table), as this would seem a better way.

Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.