cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
laurab92
Frequent Visitor

Mix and Max in an IF

Hello,

 

I have a date table with all dates from 2017-2021.  

I'd like to add a slicer for the "Year", and then use whichever values are selected to create a calculated column to flag if a row falls into the minimum year or maximum year selected, or blank if neither.

 

I have tried using both the year column in my date table ('Data Selection') and a new join table with just the list of years ('CompYears'), joined and not joined to the date table, as the slicer.

 

My current formula is:

 

IsDisplayType = IF(value('Data Selection'[YearSelected])=value([MaxY]), "Max",IF(value('Data Selection'[YearSelected])=value([MinY]),"Min",""))

 

Where I have replaced the [MaxY] and [MinY] with various other measures/calculations-
Attempt 1 Measures
[MaxY]= max([CompYears]) and [MinY]= max([CompYears])
Attempt 2 Measures
YMaxV = CALCULATE(Max('Data Selection'[YearSel]),ALLSELECTED('Data Selection'[YearSel]))  and  
YMaxV = CALCULATE(Max('Data Selection'[YearSel]),ALLSELECTED('Data Selection'[YearSel]))

plus others similar to the above, testing with operations such as FILTER, FILTERS, SELECTEDVALUE.

 

If I put 2 cards on the same page of any of my tested min/max fields, these update perfectly with the min & max of the slicer selection, but if I pull my list of dates into a table on the same page, everything either is labelled "Max" or "", no "Min" coming through.

 

I'm sure I'm missing something very obvious, I just cant figure out what!

Thanks in advance,
Laura

1 ACCEPTED SOLUTION

Hi @laurab92 ,

 

Create 2 measures as below:

_Revenue GBP min = 
CALCULATE(SUM('Invoices'[Revenue GBP]),FILTER(ALL(Invoices),'Invoices'[Year]=MINX(ALLSELECTED('Comp Years'),'Comp Years'[Year])))
_Revenue GBP max = 
CALCULATE(SUM('Invoices'[Revenue GBP]),FILTER(ALL(Invoices),'Invoices'[Year]=MAXX(ALLSELECTED('Comp Years'),'Comp Years'[Year])))

And you will see:

v-kelly-msft_0-1614232272832.png

 

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

7 REPLIES 7
v-kelly-msft
Community Support
Community Support

Hi  @laurab92 ,

 

Put the years of CompYears in the field of a slicer and create a measure as below:

 

Measure = 
IF(SELECTEDVALUE('CompYears'[Year])=MAXX(ALL(CompYears),'CompYears'[Year])&&SELECTEDVALUE(CompYears[Year])=MAX('Data Selection'[Year]),"Max",
   IF(SELECTEDVALUE('CompYears'[Year])=MINX(ALL(CompYears),'CompYears'[Year])&&SELECTEDVALUE(CompYears[Year])=MAX('Data Selection'[Year]),"Min",""))

 

And you will see:

 

v-kelly-msft_1-1613376873432.pngv-kelly-msft_2-1613376881552.pngv-kelly-msft_3-1613376890802.png

 

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

@lbendlin  @v-kelly-msft 

 

https://drive.google.com/file/d/18nKRREfgE5q1Agz5kv-Rl1hIDRtNdlKx/view?usp=sharing

Please see link to a sample workbook - I hope it works!

 

I have set up how my file is working currently, with the filters I'd like to be using set up as hardcoded in the data.  This means I can only see 2021 & 2020 data, as these are the data rows the flags are applied against (current & prior in this e.g. rather than min/max).

What I'd looking for is that Min/Max flag to be dynamic, driven by a Year slicer.

I understand that columns are not going to help me here.

 

On the tab "vaguely working" I have got the table to display the results I'd expect from various trialling of codes/formula.

However I can't get this to translate into a filter.

 

Many thanks for looking.

 

Hi @laurab92 ,

 

Create 2 measures as below:

_Revenue GBP min = 
CALCULATE(SUM('Invoices'[Revenue GBP]),FILTER(ALL(Invoices),'Invoices'[Year]=MINX(ALLSELECTED('Comp Years'),'Comp Years'[Year])))
_Revenue GBP max = 
CALCULATE(SUM('Invoices'[Revenue GBP]),FILTER(ALL(Invoices),'Invoices'[Year]=MAXX(ALLSELECTED('Comp Years'),'Comp Years'[Year])))

And you will see:

v-kelly-msft_0-1614232272832.png

 

 

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

Hi Kelly, 

Thank you so much for your suggestion.  I just trialled this, and it's not fully what I'm looking for - the Min & Max works perfectly for the actual minimum & maximum of the full list of values, however I'd like it to recognise the minmum and maximum of what is being filtered - so if I select 2018, 2019 and 2020, regardless of 2017 & 2021 existing in the table, 2018 is recognised as the minimum and 2020 the maximum.

 

lbendlin
Super User III
Super User III

Please provide sample data in usable format (not as a picture) and show the expected outcome.

lbendlin
Super User III
Super User III

"I'd like to add a slicer for the "Year", and then use whichever values are selected to create a calculated column "

 

Let me stop you right there.  Filters cannot create calculated columns.

OK - I have attempted both measures and calulcated columns, do you have any suggestions for how I might use a measure to achieve what I am looking for?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors