Forum Discussion

jbuckley's avatar
jbuckley
Frequent Visitor
7 years ago

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!

 

Need Help

4 Replies

  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
      jbuckley
      Frequent Visitor
      7 years ago

      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.