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
BST
Frequent Visitor

What if parameter - really many of them - trying to use a table instead

hello,

I have table, where the columns via a measure can be affected by 'a what if parameter' in slicer.

 

For each row (product) a new slicer and 0-100 'what if parameter'' is required.

 

To reduce the number of 'what if parameter' I am trying this approach:

 

Importing a 'slicer-table' with a column for each product with the values ranging from 0-100.

Each column is then used in a slicer.

 

The problem is when I take the 'selectedvalue' for use in a measure, I cannot get the individual values for each product slicer:

They seem to filter each other out - unless they have the same value.

 

I guess this is because 'selectedvalue' cannot return a value if multiple values are present.

 

Is there a way to filter the 'slicer-table' such that it takes product 1235's slicer value for product 1235 and product 1236's slicer value for product 1236 etc.?

 

Thanks - Bomeasure.png

 

Then I use the each column 

1 ACCEPTED SOLUTION

Hi @BST ,

 

You can upload it to a cloud drive and share it to us by download link. In fact, I have doubt whether there is a smart solution for your case. You need two what-if slicers for a product and you need to make sure they will not effect each other. You can get one value by selectedvalue function or a list of value by values function , even you can use min and max function to get two values from a slicer. But in your case, you need to get more than two values from a what-if slicer . 

 

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

 

Best Regards,

Dedmon Dai

 

View solution in original post

5 REPLIES 5
AllisonKennedy
Super User
Super User

@BST the SelectedValue function looks at the entire column, and if all columns are in the same table, as soon as you filter one slicer (ie Product 1235) for a selectedvalue, that will filter all the other columns too - so you'll be unable to choose a different value in that slicer. 

 

I think the best way would be to create them as separate tables/what-if parameters. How many do you want/need?


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi Allison, thanks for the answer - it sort of confirmed what I could not express 😉

 

Regarding your question: Currently I have 2 x 9 +2 x 5 = 28 separate what-if parameters.

Pending how management prefer the final report we could come up to as many as a 100.

 

Do you have a smart solution?

 

Bo

Hi @BST ,

 

Would you please show us some sample pbix and your expected output for reference? Then we may can come up with some solutions.

 

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

 

Best Regards,

Dedmon Dai

screenshot pbix.pngHi again,

sorry for the delay - took a while make the sample Pbix.

 

In the attached** pbix we have 10 different 'Top group nr'.

 

Each of these have a

  • what-if for the quantity (fx year 0)
  • a what-if for %-change in quantity (fx year 0+1)
  • Giving 10*1 +10*1=20 whaf-ifs

 

The what-if for %-change could also be applied to year 0+2, 0+3, 0+4, and 0+5.

 

Please ask me to clarify if the purpose of the what-ifs is unclear.

 

Thanks

Bo

 

** I have trouble uploading the sample file - is this not an option? For now I have uploaded a screenshot.

Hi @BST ,

 

You can upload it to a cloud drive and share it to us by download link. In fact, I have doubt whether there is a smart solution for your case. You need two what-if slicers for a product and you need to make sure they will not effect each other. You can get one value by selectedvalue function or a list of value by values function , even you can use min and max function to get two values from a slicer. But in your case, you need to get more than two values from a what-if slicer . 

 

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

 

Best Regards,

Dedmon Dai

 

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.