Finding Slicer Min with Multiple Selections

[Anonymous]

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!

[v-lili6-msft]

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

Best Regards,

Lin

[Anonymous]

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?

[Anonymous · Accepted solution]

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.

[v-lili6-msft]

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

[Anonymous]

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)Incorrect max number (71) due to row context for item B.

[Anonymous]

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.

[Anonymous]

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.