Simplify pick match Formula

[Martin22]

Hi all,

I encounter (again) a performance issue with a graph, because of a specific formula.

Here is some context : the graph is a stacked bar chart.
The goal is to allow the user to choose a temporal axis, the choices being to show the entire data either by Months, Weeks, or Days.
We have a field 'Period' we created (Inline) that allows us to select each choice : 'Month', 'Week', 'Date'.

The measure in the chart is a number of transports, and for the same transport we have both a departure and an arrival date, with corresponding fields for weeks and months.
As a result, we have as first dimension the following formula, as second dimension : the Type ('arrival' or 'departure') of transport, and the number of transports as measure.
The stacked part is about showing for each date/week/month the number of arriving and departing transports.

Is there a way to simplify the formula, for a performance gain ?

If ([Type]='arrival',
PICK(MATCH([Period],'Month','Week','Date'),[Year Month YYYYMM Arrival],[Year Week YYYYWW Arrival],[Date Arrival]),
PICK(MATCH([Period],'Month','Week','Date'),[Year Month YYYYMM Departure],[Year Week YYYYWW Departure],[Date Departure]))

Thanks,

Martin.

[sunny_talwar]

Assuming Type and Period are both Island field dimensions and not connected to anything else in your data... you can actually create a new table (and remove the existing ones) like this

Table:
LOAD * INLINE [
    Type, Period, DimName
    arrival, Month, Year Month YYYYMM Arrival
    arrival, Week, Year Week YYYYWW Arrival
    arrival, Date, Date Arrival
    departure, Month, Year Month YYYYMM Departure
    departure, Week, Year Week YYYYWW Departure
    departure, Date, Date Arrival
];

After that, you can change your dimension to this

$(='[' & DimName & ']')

But please note that this will only work when you have selected a single value in Type and a single value in Period.

[Martin22]

Thank you for your answer.

In this case, there is always one selected value for Period, but not for Type (the stacked bar chart is showing both types simultaneously).

Is there a way to adapt your solution to this case ?

[sunny_talwar · Accepted solution]

I am assuming Type is the dimension in your chart? If that is true, you can try this

Table:
LOAD * INLINE [
    Type, Period, DimName
    Month, Year Month YYYYMM
    Week, Year Week YYYYWW
    Date, Date
];

and then may be this

Pick(Match(Type, 'Arrival', 'Departure'), $(='[' & DimName & ' Arrival]'), $(='[' & DimName & ' Departure]')

[Martin22]

Thank you for the solution, the charts are loading faster.

Regards,

Martin.