Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 acc...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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.