Forum Discussion
Field Parameter when used in filter pane not working for TOPN filters
Hi,
I have created a visual as below:
Now I want to filter this visual by bottom 10 worst performers Site Name by Variance parameter created. FYI the variance Field parameter table shown below:
So I dragged that field parameter column into filter on this visual 'By value'. But its not showing top10 or bottom 10 instead it is showing all site name.
Please advise!
PFA file here Portfolio Performance - v2.15 (1) - Copy.pbix
Thanks in advance Sir!
Composite key is not part of the model, it's unpacked in background query for visuals. Thus we need to return somehow parameter value into the model to apply it to filters.
As variant we may create two measures.
Selected Parameter = VAR SelectedParameter = SELECTCOLUMNS ( SUMMARIZE ( 'Variance', 'Variance'[Parameter], 'Variance'[Parameter Fields] ), 'Variance'[Parameter] ) RETURN IF ( COUNTROWS ( SelectedParameter ) = 1, SelectedParameter )and
Performance = SWITCH ( TRUE, [Selected Parameter] = "Target Unit Variance", [Target Unit Variance], [Selected Parameter] = "Budget Unit Variance", [Budget Unit Variance], [Selected Parameter] = "Budget Cost Variance", [Budget Cost Variance], [Selected Parameter] = "Target Cost Variance", [Target Cost Variance], [Selected Parameter] = "Budget Carbon Variance", [Budget Carbon Variance], [Selected Parameter] = "Target Carbon Variance", [Target Carbon Variance], [Selected Parameter] = "Target Unit Variance (TimeSeries)", [Target Unit Variance (TimeSeries)], [Selected Parameter] = "Target Cost Variance (TimeSeries)", [Target Cost Variance (TimeSeries)], [Selected Parameter] = "Budget Unit Variance (TimeSeries)", [Budget Unit Variance (TimeSeries)], [Selected Parameter] = "Budget Cost Variance (TimeSeries)", [Budget Cost Variance (TimeSeries)], [Selected Parameter] = "Budget Carbon Variance (TimeSeries)", [Budget Carbon Variance (TimeSeries)], [Selected Parameter] = "Target Carbon Variance (TimeSeries)", [Target Carbon Variance (TimeSeries)], 0 )and use them with visuals
where
Perhaps not good from maintenance point of view, but at least it works.
Composite key is not part of the model, it's unpacked in background query for visuals. Thus we need to return somehow parameter value into the model to apply it to filters.
As variant we may create two measures.
Selected Parameter = VAR SelectedParameter = SELECTCOLUMNS ( SUMMARIZE ( 'Variance', 'Variance'[Parameter], 'Variance'[Parameter Fields] ), 'Variance'[Parameter] ) RETURN IF ( COUNTROWS ( SelectedParameter ) = 1, SelectedParameter )and
Performance = SWITCH ( TRUE, [Selected Parameter] = "Target Unit Variance", [Target Unit Variance], [Selected Parameter] = "Budget Unit Variance", [Budget Unit Variance], [Selected Parameter] = "Budget Cost Variance", [Budget Cost Variance], [Selected Parameter] = "Target Cost Variance", [Target Cost Variance], [Selected Parameter] = "Budget Carbon Variance", [Budget Carbon Variance], [Selected Parameter] = "Target Carbon Variance", [Target Carbon Variance], [Selected Parameter] = "Target Unit Variance (TimeSeries)", [Target Unit Variance (TimeSeries)], [Selected Parameter] = "Target Cost Variance (TimeSeries)", [Target Cost Variance (TimeSeries)], [Selected Parameter] = "Budget Unit Variance (TimeSeries)", [Budget Unit Variance (TimeSeries)], [Selected Parameter] = "Budget Cost Variance (TimeSeries)", [Budget Cost Variance (TimeSeries)], [Selected Parameter] = "Budget Carbon Variance (TimeSeries)", [Budget Carbon Variance (TimeSeries)], [Selected Parameter] = "Target Carbon Variance (TimeSeries)", [Target Carbon Variance (TimeSeries)], 0 )and use them with visuals
where
Perhaps not good from maintenance point of view, but at least it works.
Hi Sir,
Thanks for your amazing response!🙂
This is the gamechanger solution for our report. After so many testing, we have come to a conclusion, this is the efficient solution as far performance is considered.
I will accept and mark it as solution.Just to confirm,
1)what do you mean by composite key here?
2) First measure is just used for title card right ? it doesn't in any affect the second solution? what i mean is we can also modify your dax as below correct?Thanks sir!
SergeiBaklan
