Summarise counts of counts

[bn8959]

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:

SELECT
t.internalcount,
COUNT(t.internalcount) AS countsum
FROM
( SELECT
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.

[LaurentCouartou]

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

[bn8959]

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

[v-ljerr-msft]

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.

Regards

[a4apple]

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

http://www.daxpatterns.com/dynamic-segmentation/

[bn8959]

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.

[a4apple]

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.

[bn8959]

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.