Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Worldbreaker
Helper I
Helper I

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 value.

 

I created an identifier column that identifies the max value as 1, if it's not, its a 0.

I also have created an identifier column that identifies the min value as 1, if it's not, its a 0.

 

the problem I am having is when I use filters.

 

for example, I want to see only the values from the 1st quarter only of one group.

 

the result is not showing the minimum since it thinks that it is not the minimum, it still sees the true minimum without the filters.

 

without filter, it is showing the minsample pb.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

with filter, it's not showing the min based on the filter

 

 

sample pbi.jpg 

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft Employee
Microsoft Employee

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. Smiley Happy 

 

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.

 

r4.PNG

2, Result with filter.

 

 

r3.PNG

Regards

View solution in original post

3 REPLIES 3
v-ljerr-msft
Microsoft Employee
Microsoft Employee

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. Smiley Happy 

 

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.

 

r4.PNG

2, Result with filter.

 

 

r3.PNG

Regards

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

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. Smiley Happy

 

Regards

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.