cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bn8959 Frequent Visitor
Frequent 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:

 

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.

7 REPLIES 7
a4apple Regular Visitor
Regular Visitor

Re: Summarise counts of counts

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

http://www.daxpatterns.com/dynamic-segmentation/
bn8959 Frequent Visitor
Frequent Visitor

Re: Summarise counts of counts

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 Regular Visitor
Regular Visitor

Re: Summarise counts of counts

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 Frequent Visitor
Frequent Visitor

Re: Summarise counts of counts

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.

Re: Summarise counts of counts

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

bn8959 Frequent Visitor
Frequent Visitor

Re: Summarise counts of counts

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

 

Capture.PNG

Microsoft v-ljerr-msft
Microsoft

Re: Summarise counts of counts

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

 

Regards

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors