Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am having trouble with the Top 1 filter for a visual that is supposed to show the top performing month by profit and group the collumns by year.
For some reason it will take the top performing month of year 2013 and then the same month from year 2014 (the top performing month for 2014 is December)
I don't understand what I am doing wrong.
Solved! Go to Solution.
You can use a DAX measure like this one to get that result. This find the max for all months in the given year and returns blank for all but the max month (and are filtered out in the visual). The visual uses the yearmonth column from my Date table and this measure on the Y axis.
Max Month Only =
VAR vThisResult = [Total Qty]
VAR vThisYear =
MAX ( 'Date'[Year] )
VAR vMaxThisYear =
CALCULATE (
MAXX ( VALUES ( 'Date'[YearMonthShort] ), [Total Qty] ),
ALL ( 'Date' ),
'Date'[Year] = vThisYear
)
RETURN
IF ( vThisResult = vMaxThisYear, vThisResult, BLANK () )
Pat
You can use a DAX measure like this one to get that result. This find the max for all months in the given year and returns blank for all but the max month (and are filtered out in the visual). The visual uses the yearmonth column from my Date table and this measure on the Y axis.
Max Month Only =
VAR vThisResult = [Total Qty]
VAR vThisYear =
MAX ( 'Date'[Year] )
VAR vMaxThisYear =
CALCULATE (
MAXX ( VALUES ( 'Date'[YearMonthShort] ), [Total Qty] ),
ALL ( 'Date' ),
'Date'[Year] = vThisYear
)
RETURN
IF ( vThisResult = vMaxThisYear, vThisResult, BLANK () )
Pat
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |