Forum Discussion

mmbaroja's avatar
mmbaroja
Frequent Visitor
4 years ago

Aggregation on rows with user selected filters

Hello:

I am trying to recreate an analysis done previously with QlikView:

A sales offer includes five types of offer (depending on the team who worked on the offer), many features and a few versions. There are five types of sales offers . The purpose of the report is to compare two types of an offer. For doing so, the report includes three slicers (offer id, offer type 1 and offer type 2). Once selected a value for the slicers, the report should display:

 

Group by offer, feature, feature value for offer type1, feature value for offer type 2 and concatenatex for revisions.

 

The point is this table is generated once the user has selected offer type 1 and offer type 2, so I can't create it with Power Query and I can't create a calculated table with DAX either; create a table with all the combinations of all offer types is impossible due to the size.

 

As far as I know, I should create three DAX measures (Revisions, Value type 1 and value type 2), but a measure returns an scalar value, so how I am supposed to return for one offer and one feature more than one value for each measure?

 

I am probably missing something because I see no way out of this. And it is probably due to my short experience with DAX and PowerBI. 

 

 

Many thanks in advance,

 

MM.

10 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    If you only have five types you can select two from, there are only 10 possible pairs to pick from, so pre-calculating all of them doesn't necessarily seem impossible but, ideally, there are better options.

     

    Are columns D:E calculated somehow or are the values already determined a priori?

     

    It would help greatly if you could include some sample starting data you've got before you start trying to group and concatenate.