Mix and Max in an IF

[laurab92]

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

[v-kelly-msft]

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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[laurab92]

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

[v-kelly-msft · 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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[laurab92]

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]

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

[lbendlin]

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

[laurab92]

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?