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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bn8959
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:

 

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
LaurentCouartou
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:

 

Capture.PNG

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

a4apple
Helper I
Helper I

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

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

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

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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