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.
Source: Tabular model 🙂
I have 'Year Target' table containing columns: Department - Date - TargetArea - Min - Mid - Max.
I have made measure for min, mid and max by TargetArea which I can use on report.
(e.g. Material Y Target max:=CALCULATE(AVERAGE('YearTarget'[Max]);'YearTarget'[TargetArea]="Material";'Year Target'[Max]<>0)
Date in this table is for 1st day of Jan in selected year (e.g. 2020-01-01, 2019-01-01 etc.).
If I now link that date field to DimDate I will get target measures only for that month.
How to update measures so that I have correct target for 2020 even I have any of the months selected ?
And if I select any month from 2019 it shows targets for that year?
Solved! Go to Solution.
[Good Measure] =
CALCULATE(
AVERAGE( YearTarget[Max] ),
// You want to wrap conditions in KEEPFILTERS
// (almost) every time you know you're going to
// slice and dice by the attribute involved.
KEEPFILTERS( YearTarget[TargetArea] = "Material" ),
YearTarget[Max] <> 0, // I would be careful with this condition...
ALL( DimDate ),
VALUES( DimDate[Year] )
)
Best
D
[Good Measure] =
CALCULATE(
AVERAGE( YearTarget[Max] ),
// You want to wrap conditions in KEEPFILTERS
// (almost) every time you know you're going to
// slice and dice by the attribute involved.
KEEPFILTERS( YearTarget[TargetArea] = "Material" ),
YearTarget[Max] <> 0, // I would be careful with this condition...
ALL( DimDate ),
VALUES( DimDate[Year] )
)
Best
D
When you put this measure on a graphic (a table say) Material Y Target max:=CALCULATE(AVERAGE('YearTarget'[Max]);'YearTarget'[TargetArea]="Material";'Year Target'[Max]<>0)
then the filter context it runs it is limited to the row in the table....which is why is shows only that month
CALCULATE can change the external filter context by add an ALL argument
CALCULATE(AVERAGE('YearTarget'[Max]);'YearTarget'[TargetArea]="Material";'Year Target'[Max]<>0;ALL('YearTarget'))
or maybe ALL('YearTarget'[Max])
this will remove the filter on the YearTarget table and you should get the full average.
Help when you know. Ask when you don't!
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |