cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

Finding Slicer Min with Multiple Selections

Hi,

 

I have a slicer with multiple options:

Jun 1

Jun 2

Jul 1 (selected)

Jul 2 (selected)

Aug 1

 

I have formulas to determine what min and max months are selected and make calculations accordingly. These calculations work fine until an item has data in one month but not another (ex: if boats were sold in Jul2 but not Jul1). When this happens, the formula thinks Jul2 is the min and the max. I need PowerBI to keep all filter contexts except for this item column so that it maintains the same min. 

 

Any ideas on how to find the min and max selected filters are while ignoring just one column? Thanks!

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: Finding Slicer Min with Multiple Selections

Ultimately, I solved this by taking the table responsible for the slicer and offloading it into it's own table separate from the data getting filtered on. I was then able to run my min/max calculations on the offloaded table which was no longer being filtered down by my data as the relationship was 1 way from the offloaded table to the new table.

View solution in original post

7 REPLIES 7
Highlighted
Community Support
Community Support

Re: Finding Slicer Min with Multiple Selections

Hi@ SBuri

I have tested on my side, but not reproduce the issue. If possible, please share detail steps for us to reproduce the issue. 

please share some data sample and the formula and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Resolver II
Resolver II

Re: Finding Slicer Min with Multiple Selections

Hi @v-lili6-msft Lin,

 

Thanks for your response. I've uploaded an example Excel dataset linked to an example Power BI file.

 

July Q1-Q2 contains data for item B, but item B has no data for July Q3-Q4. This causes the max and min formula to pick July Q1-Q2 (71) as the max and min date for item B, despite the secondary slicer selection which should make the max selection July Q3-Q4 (72). In the actual dataset the min/max formula are intermediary measures to determine whether other measures should be compared within the same month or within separate months.

 

The below screenshots illustrate the challenge - 

Correct max/min number detected (71-72)Correct max/min number detected (71-72)Incorrect max number (71) due to row context for item B.Incorrect max number (71) due to row context for item B.

 

 

 

 

Highlighted
Resolver II
Resolver II

Re: Finding Slicer Min with Multiple Selections

I believe I've found the solution. I offloaded the dates column alone within M:

  1. Right click column
  2. Add as new query
  3. Within the new query right click
  4. Convert to table

Then I input that column as the slicer and used the below equation to solve:

FormulaName = CALCULATE(MAX(OriginalTable[DateNumbers]), ALLEXCEPT(NewTable, NewTable[NewColumn]))

 

I'll circle back to confirm or delete this.

Highlighted
Resolver II
Resolver II

Re: Finding Slicer Min with Multiple Selections

While the above post works in the example, it fails to work in the real file. In the example the table relationship is 1:1, no issue. In the real example the relationship is 1:Many, for some reason this causes Power Bi to error saying that because I'm choosing allexcept(newcolumn) and the new column is the only column in the table there is nothing to return. Any other ideas?

I've updated the example file to version 3 to illustrate the 1:* issue.

Highlighted
Community Support
Community Support

Re: Finding Slicer Min with Multiple Selections

Hi@ SBuri

You may try to use ALLEXCEPT function and Edit interactions function .Visual interactions can be used to change how visualizations on a report page impact each other.

max_DateSelected = CALCULATE( MAX(Sheet1[Dates_order]),ALLEXCEPT(Sheet1,Sheet1[Dates]))
min_DateSelected = CALCULATE( MIN(Sheet1[Dates_order]),ALLEXCEPT(Sheet1,Sheet1[Dates]))

7.png8.png

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Resolver II
Resolver II

Re: Finding Slicer Min with Multiple Selections

Hi @v-lili6-msft Lin,

 

This works in a 1:1 relationship, it does not work in a 1:* relationship unfortunately. It throws an error saying the argument passed to the allexcept cannot be all the columns in the allexcept table. I added in additional columns and this made the error go away, however it's back to detecting the wrong max and min slicer selections.

 

Also, simply hiding the effect the slicer selection is having on the max and min is not an option as the calculation itself is merely an intermediary measure for other calculations. The number itself must be correct.

 

Does anyone have any ideas on how to get the max/min of a slicer selection irrespective of whether or not all items exist in both selections?

 

Highlighted
Resolver II
Resolver II

Re: Finding Slicer Min with Multiple Selections

Ultimately, I solved this by taking the table responsible for the slicer and offloading it into it's own table separate from the data getting filtered on. I was then able to run my min/max calculations on the offloaded table which was no longer being filtered down by my data as the relationship was 1 way from the offloaded table to the new table.

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors