Hi Mynda
I have encountered a few challenges when trying to build a dashboard of house prices - snapshot attached - as follows:
1. Using DAX measures to calculate regional and country aggregates, the only way I seem to be able to feature local authority, regional and country data in the top left "All properties" chart is by including region and country as x-axis fields. This then has the effect of causing the chart to stretch over several chart widths ...
2. Using quick measures to create Annual house price inflation metrics ("Annual house price inflation" chart top right), I get some extraneous -100% figures for forthcoming periods with no data. What is the best way of suppressing these figures - do I use a DATEBETWEEN, LATESTDATE or some other function to hide such information?
3. In a a similar vein, I sometimes want to be able to suppress latest values, eg if only a few observations have been made this month which would be misleading. So, for example, how would I go about suppressing the latest month's observations?
4. Again, looking at the "Annual house price inflation" chart top right, it seems that the Quick Measure for % change year on year can only be used with Calendar [Date] column values, whereas I would like to show using the YearMonthName variable used in the bottom right chart.
Any thoughts much appreciated. Also, given the various uses I am trying to make of the local authority data, does it still make sense for me to be persevering with all these "on the fly" DAX measures - I'm currently up to about 25 of the perishers!
Thanks
Bob
Hi Bob,
1. I'd have thought the house price trends should show either all data or that of the LGA area chosen in the Slicer. No need for the local authority etc. to b part of the chart.
2. Yes, if the quick measures fall short, then you need to write your own custom DAX measures that handle anomalies.
3. You'd need to write a DAX measure that used a specific time period.
4. The quick measures currently only work with Power Pivot date hierarchies. If you want to use your YearMonthName field then you'll need to write your own DAX measure. See session 10 of the Power Pivot course for Time Intelligence DAX functions.
Mynda
