Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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!
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!
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:
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.
Please provide sample data in usable format (not as a picture) and show the expected outcome.
"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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |