topic Re: Aggregation on rows with user selected filters in DAX Commands and Tips

Aggregation on rows with user selected filters

mmbaroja — Sun, 14 Nov 2021 17:42:22 GMT

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.

Re: Aggregation on rows with user selected filters

AlexisOlson — Sun, 14 Nov 2021 18:45:25 GMT

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.

Re: Aggregation on rows with user selected filters

mmbaroja — Sun, 14 Nov 2021 20:52:38 GMT

You are right; impossible is not the best word. 🙂

I would say not desirable at all. I see this as a generic problem so I could have 100 types and then it wouldn't be an option.

Columns D:E are determined a priori. I just uploaded a sample data:

https://lksscoop-my.sharepoint.com/:x:/g/personal/m_baroja_lksscoop_onmicrosoft_com/ETEzg7DzztVOvPY4SkGEOvQByQ2qLojrv2Q9CEVqQNe7_g?e=QJChM0

There are over 100 features for an offer but i just included 9 of them (columns D:L).

Thanks,

MM.

Re: Aggregation on rows with user selected filters

AlexisOlson — Mon, 15 Nov 2021 16:53:13 GMT

Can you share what rows and columns would correspond to your initial example? I'm having trouble matching up this spreadsheet with that example.

Re: Aggregation on rows with user selected filters

mmbaroja — Mon, 15 Nov 2021 18:08:19 GMT

Sure!!

Find below an image with a simple example. One offer, one feature (just T_Max) and two type of offer: INC (user selected it as type offer1) and INP (user selected it as type offer 2).

Left side is the spreadsheet and right side is the initial example (what the report should show):

So the mapping from the spreadsheet I uploaded would be:

A: Num_Offer is column Offer of initial example

B: Version. After applying concatenatex this is Revisions of initial example.

C: Offer Type. This is the type of offer and it is used to know where to put values of features columns D and E of spreadsheet.

D-L: This is are a few features of the offer. The name of the feature is the value of column B of the initial example. Values from D-L (spreadsheet) are taken into columns D-E (initial example) based on the offer type selected by the user as the image above shows.

Re: Aggregation on rows with user selected filters

AlexisOlson — Mon, 15 Nov 2021 19:16:59 GMT

If you unpivot the features and define a custom column that groups the versions, this should be doable.

See attached.

Re: Aggregation on rows with user selected filters

mmbaroja — Tue, 16 Nov 2021 08:33:44 GMT

I see your approach but the value of the feature for offer type 2 should be included when grouping. For T_MAX, version 6 changes from 150 to 150000 when comparing INC with INP. But the report shows two rows for 6 (empty -> 150000 and 150 to empty)

I also changed the value for 12 and the same:

As far as I can see, the value for offer type 2 should be included for grouping. And this means, combinations of types should be created as well. And this leads us to the original approach.

Am I correct?

Re: Aggregation on rows with user selected filters

AlexisOlson — Tue, 16 Nov 2021 15:15:41 GMT

You'll have two rows regardless (either "6" and "9" or else "6" and "6,9"). The advantage of the way I did it is that the version strings can be pre-calculated. If you can come up with a better approach, then go for it.

Re: Aggregation on rows with user selected filters

mmbaroja — Thu, 18 Nov 2021 07:52:25 GMT

Yes, I am afraid I should go for the initial approach and get the combinations, and the grouping for each feature. What I really wanted to know is that there is no possibility of doing it with just measures as it is done with Qlik. It's so easy; you just create a measure that returns multiple values and get the aggregation level that you need. You don't need to "pre-cook" all the data.

I guess there are other scenarios where PowerBI is easier than Qlik. Many thanks for your help and your time. Really appreciated.

Re: Aggregation on rows with user selected filters

AlexisOlson — Thu, 18 Nov 2021 15:50:06 GMT

The difficulty here is that you are essentially wanting to treat the list of versions as both a dynamic measure and a dimension at the same time. This is a pretty unusual situation.

Re: Aggregation on rows with user selected filters

Icey — Thu, 02 Dec 2021 08:18:25 GMT

Hi mmbaroja ,

I'm afraid this can't be achieved in Power BI.

Why do you want to display data like this? Can it be changed another way?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.