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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tontaube2
Helper IV
Helper IV

Compare different products; User shall choose 2 to 4 products to compare

Hi,

 

I want to compare e.g. Product A to B, C and D. The user can choose how many products to compare by using a slicer and select a parameter 2-4.

Depending on that, 2-4 additional fields shall appear to make the user choose a product in them. Should I make Bookmarks, so that 2-4 dropdown appears depending on the value of the parameter? Or is there a "better way"?

 

And further onwards: what's a better/the best way to make the "actual" comparisons? Right now, I think 2-4 additional fact tables containing the same data. Ideally referenced instead of copied so that I don't quadruple the amount of data. Or would it not matter because of Data compression?

 

But it seems "clumsy" to add 2-4 fact tables, connect them to lookup/dim-tables... create a lot of DAX-Formulas which include "switch". Surely there's an easier way. I hope. 

Any hints? Suggestions?

 

As this is a question about PowerQuery/DataModellimg as well as Desktop/Visualization, I've posted it in the forum which is more-frequented.

 

Bye

 

Michael

1 ACCEPTED SOLUTION

Hi @Tontaube2 

 

For the first question, you can select multiple companies in a slicer for comparison at the same time. You may need to create multiple Dim tables for companies if you want to use multiple dropdowns, which I would not recommend because it will make the model complicated unnecessarily. Under Selection controls, turn off Single select to select multiple values at a time.

 

For the second question, if you put company in the Columns of a matrix visual, only the selected companies from the slicer will show in the matrix. You don't need to use extra formulas.

020202.jpg

 

For the third question, measures will work automatically and dynamically according to the context they are in. Usually you don't need to tell them which values have been selected because the slicer has already influenced the context of a measure. However, if you want to get the selected values, you can use functions like SELECTEDVALUE(), VALUES(), MAX()...

 

It's difficult to tell which formula will be helpful without knowing what data and expected result is like. 

 

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

@Tontaube2 First question, let's use select 4 in the slicer, what 4 products will be available to compare? What would be the business rule? Assume you have 100 products, use selected 4 to compare in the slicer, now which 4 product to pick from 100. Let's get this sorted out first and then we can look into solving it.

 

2nd, you don't need to create different fact tables, you can compare using measures. It is not a good choice to duplicate your fact table but use measure, apply the business logic, and go from there.

 

But I think you need to answer some basic questions before even get to the solution.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi Parry2k,

 

thank you for your help!

 

I want to compare products of Insurance companies.

In a first slicer the user has selected the product: E.g. liability insurance, car insurance and so on...

 

On a second "level" the user shall choose between 2 and 4 companies to compare on that product: E.g. their liability insurance begins at and tops out at...

 

Lets say I have 4 dropdown-fields/slicers on this second level. In each the user can select a company via dropdown - or leave it empty. 

 

So basically there are 3 questions:

A) The dropdown for the second level-slicers mustn't include the companies already selected in another dropdown. I have no idea about the formula to do this.

C) Which formula do I need to dynamically add the chosen-company-columns to a matrix-visualization?

D) Can you give me a hint about "how to choose the proper company-values from the fact-table" instead of having multiple fact tables? Which formula do I need for the measure?

 

I am not looking for "complete solutions". But I am a Newbie and I don't know for WHICH formula I need to look into the details...

 

Bye

 

Michael

 

 

Hi @Tontaube2 

 

For the first question, you can select multiple companies in a slicer for comparison at the same time. You may need to create multiple Dim tables for companies if you want to use multiple dropdowns, which I would not recommend because it will make the model complicated unnecessarily. Under Selection controls, turn off Single select to select multiple values at a time.

 

For the second question, if you put company in the Columns of a matrix visual, only the selected companies from the slicer will show in the matrix. You don't need to use extra formulas.

020202.jpg

 

For the third question, measures will work automatically and dynamically according to the context they are in. Usually you don't need to tell them which values have been selected because the slicer has already influenced the context of a measure. However, if you want to get the selected values, you can use functions like SELECTEDVALUE(), VALUES(), MAX()...

 

It's difficult to tell which formula will be helpful without knowing what data and expected result is like. 

 

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

Yes, thank you very much!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.