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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jbuckley
Frequent Visitor

group data by dimension (aggr function in qlik sense)

Hello,

 

I'm trying to understand how I can create a dimension based upon a measure. Example below...

 

Transaction|Site

12345|A

12346|A

12347|A

12348|A

12349|B

12350|B

12351|C

12352|C

 

I can create a table which easily counts the number of transactions by site...

 

Site|Transactions

A|4

B|2

C|2

 

What I would now like to do is to summarise the number of sites by the transactions they have... i,e,

 

Transactions|Sites

2|2 (i.e. B and C)

4|1 (i.e. A)

 

Coming from Qlik, there is an Aggr function where you can specify the Transactions in the last table as:

Aggr(Count(Transactions),Site)

 

I'm not sure how this is replicated in Power BI.

 

Thanks for any help!

 

4 REPLIES 4
d_gosbell
Super User
Super User

You could just create a many to one relationship between table1 and table2.

 

Then create a distinctcount measure on table1.

 

eg.

 

Count of Sites = DISCTINCTCOUNT( table1[Site] )

 

Then just drag this new measure and table2[Site] onto a visual

Thanks for the suggestion but I wasn't very clear in the outset.

 

I haven't made a table from the first data (a new query). I have created a table as in a visual to show the data.

 

I don't want to make a new query from the first dataset as I would like the end table to be dynamic (i.e. if I filter on a date range, the data updates). This wouldn't happen if I created a second query from the first.

Ok, that's a different scenario. You can't do grouping by a measure in PowerBI you'd just end up with the aggregate of both measures.

 

This is not quite what you are after, but you could maybe use a banding/segmentation pattern see:

 

Thanks but I'm not sure they solve what I'm looking for.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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