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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mmbaroja
Frequent Visitor

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:

 

pbi_community1.PNG

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 10
AlexisOlson
Super User
Super User

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.

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/ETEzg7DzztVOvPY4...

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

 

Thanks,

 

MM.

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

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): 

 

Capture_EJEMPLO_MENSAJE2.PNG

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.

 

 

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

 

AlexisOlson_0-1637003678521.png

 

See attached.

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) 

 

Capture_community_message1.PNG 

I also changed the value for 12 and the same:

Capture_community_message2.PNG

 

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?

Icey
Community Support
Community Support

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.

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.

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.

 

 

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors