Forum Discussion

sunilbhanushali's avatar
sunilbhanushali
New Member
4 years ago

calculate Minimum value based on Filter Selection

I want to find minimum value and put it in seperate column to show and this minimum value would change based on the selection done.

 

Scenario 1

 

 

 

When there is no seletion on Qtr value or when all Qtrs are selected, Q1 has minimum value hence last column is showing minimum value 100

 

Scenario 2

 

But now user can change filter condition on Qtr and can select Q2,Q3 and Q4 and hence minimum value will change now to that of Q2 which is 200

 

 

I am using Direct query mode to fetch the results.  Year and Amt are coming from two different tables.

 

I am trying to write DAX by creating measure for e.g. 

 

min_amt = CALCULATE(min(amt),ALLEXCEPT(table,table[Qtr])

 

but still no luck as it is still showing 100 even when I am not selecting Q1 as part of my filter condition.

Need Help

4 Replies

  • mh2587's avatar
    mh2587
    Super User
    4 years ago
    min_amt = CALCULATE(min(amt),ALLSELECTED(table,table[Qtr])

    Just change allexcept with allselected hope it will work

    • sunilbhanushali's avatar
      sunilbhanushali
      New Member
      4 years ago

      Hi,

       

      i had tried ALLSELECTED too but it didn't work. I also tried using summerize funcatin with no luck

       

      v_min_amt = 
var grpby = SUMMARIZE(
            table,
            table[Qtr]
            )
return minx(grpby,min(table[amt]))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi sunilbhanushali 

    Please try a measure like this :

     

    Min Amt = CALCULATE(MIN('Table'[Amt]),ALLSELECTED('Table'))

     

    And the final result is as shown :

    I have attached my pbix file ,you can refer to it .

     

    Best Regards

    Community Support Team _ Ailsa Tao

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