Forum Discussion
Filtered Values vs Relevant Benchmark Data - FILTER based on a SELECTEDVALUE?
Are the 2 data sets joined?
Do you have the road type benchmarks at road type level?
If not maybe you can create a dax table at road type level. Then join the road to this new table using the roadtype.
You can use something like this to add a dax table. Goto Modelling and click table.
RoadTypeSummary =
ADDCOLUMNS(
SUMMARIZE(
'RoadBenchmarks'
,RoadBenchmarks[RoadType]
)
,"DataX",CALCULATE(COUNTROWS(DataX))
,"DataY",CALCULATE(SUM(RoadBenchmarks[DataY]))
,"SDDataY",CALCULATE(STDEV.P(RoadBenchmarks[DataY]))
)You can then join to your road table.
It should filter with the road.
You can write dax from this on the road table or bring in the benchmark data to the road table using the RELATED function.
This makes it easy to write expressions that compare the current and benchmark values.
Also if you don't need it you can hide the summary table to keep the model a bit simpler.
- Anonymous8 years agoNot applicable
Thank you for your time and thoughts on this.
I followed your advice, it worked well plus it inspired me also to try something else that worked well - I duplicated the table, linked the two identical tables together and used one table for one set of data and the other for the control data.
Thanks again
Marie
- stretcharm8 years agoMemorable Member
Great stuff. Glad to be of help.