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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!

 

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
Super User

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

lbendlin
Super User
Super User

"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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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