Forum Discussion

Worldbreaker's avatar
Worldbreaker
Icon for Helper I rankHelper I
9 years ago
Solved

getting the MIN or MAX value after filtering in visualization

I have a table that shows values from 1 to 10   and I have a date column as well and group column   I want to show the values excluding the dynamic maximum value and excluding the dynamic minimum...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Worldbreaker,

     

    Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

     

    So it is not possible to create a calculate column/table can change dynamically with user selections on the report.

     

    Normally, we can create measures instead. The formulas below are for your reference. :smileyhappy: 

     

    IsMax = 
VAR maxValue =
    CALCULATE ( MAX ( Table1[Value] ), ALLSELECTED ( Table1 ) )
VAR currentValue =
    MAX ( Table1[Value] )
RETURN
    IF ( currentValue = maxValue, 1, 0 )

    IsMin = 
VAR minValue =
    CALCULATE ( MIN ( Table1[Value] ), ALLSELECTED ( Table1 ) )
VAR currentValue =
    MAX ( Table1[Value] )
RETURN
    IF ( currentValue = minValue, 1, 0 )

    1. Result without filter.

     

    2, Result with filter.

     

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Worldbreaker,

 

Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

 

So it is not possible to create a calculate column/table can change dynamically with user selections on the report.

 

Normally, we can create measures instead. The formulas below are for your reference. :smileyhappy: 

 

IsMax = 
VAR maxValue =
    CALCULATE ( MAX ( Table1[Value] ), ALLSELECTED ( Table1 ) )
VAR currentValue =
    MAX ( Table1[Value] )
RETURN
    IF ( currentValue = maxValue, 1, 0 )

IsMin = 
VAR minValue =
    CALCULATE ( MIN ( Table1[Value] ), ALLSELECTED ( Table1 ) )
VAR currentValue =
    MAX ( Table1[Value] )
RETURN
    IF ( currentValue = minValue, 1, 0 )

1. Result without filter.

 

2, Result with filter.

 

 

Regards

  • Worldbreaker's avatar
    Worldbreaker
    Icon for Helper I rankHelper I
    9 years ago

    Hi v-ljerr-msft - 

     

    Thank you for your reply.

     

    I really appreciate it.

     

    I am just having problems incorporating your DAX formula.

     

    I have updated Table1[value] with what I have on file as well.

     

    But, I am seeing the currentValue as "grey" when I put the formula in.

    compared to maxValue which is green.

     

    I am also seeing the error message, the syntax for 'VAR' is incorrect

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi Worldbreaker,

       

      Could you post the DAX formula you're using which is not working? So that I can help further investigate on the issue. It's better to share a sample pbix file which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

       

      Regards