group data by dimension (aggr function in qlik sense)

[jbuckley]

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!

[d_gosbell]

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

[jbuckley]

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.

[d_gosbell]

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:

• https://www.daxpatterns.com/dynamic-segmentation/
• https://exceleratorbi.com.au/banding-in-dax/

[jbuckley]

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