Line Chart Unfiltered Dimension

[#1]

Dear Community,

I am trying to have a fixed x-axis (time) and I would like to show only the data that is selected in the graph but not change the x-axis. At the same time I want to be able to select a date on the axis.

This is done by:

Sum({1}IF(Match(YEAR([ChartDateTime]), $(=GetFieldSelections([Jahr])) ),[PO value (custom)]))

and works fine.

However, I also want to show all data if nothing is selected.

I tried:

If(GetSelectedCount([Jahr])>0,

     Sum({1}IF(Match(YEAR([ChartDateTime]), $(=GetFieldSelections([Jahr])) ),[PO value (custom)])),

     Sum({1}[PO value (custom)])

)

But for some reason it doesn't work.

Any suggestions?

[jonathandienst · Accepted solution]

Hi

GetFieldSelections returns null if nothing is selected, the $(=...) returns an empty string, so you have a syntax error. The expression works if something is selected, because then there is no syntax error.

You could try something like

If(GetSelectedCount([Jahr])>0,

     Sum({1}IF(Index(YEAR([ChartDateTime]), '$(=Concat(DISTINCT ([Jahr])))'), [PO value (custom)])),

     Sum({1}[PO value (custom)])

)

HTH

Jonathan

[#3]

Thank you, Jonathan!

Do you know if there might be a way to improve the performance of this task?

[jonathandienst]

It's pretty hard to make specific suggestions without sight of your model and data.

Some general points to consider:

• Be aware that QV appears not to optimise or shortcut expressions, so both legs of the if clause may be recalculated every time you make selections
• Sum(If()) statements perform poorly on large data sets. Consider refactoring the expressions using set analysis (you would need a Year field for example)
• If the model structure means that the fields in these expressions come from different tables, consider flattening your table structure

HTH

Jonathan