Building a chart on the fly

[draghici1109]

Hello,

I have the following challenge:

I have the following data

LOAD * Inline [
PersonalNumber, DateChange(Month), Category
11111,20220101,3
11111,20220401,4
11111,20220701,6
];

I want to buld a bar chart with the Month as x axe and Category as y axe, yet without using calendar functions for building help tables or intervalmatch. With other words, want to build the chart "on the fly".

[marcus_sommer]

I doubt that such an approach will be sensible - why creating complex synthetic dimensions and probably not simple expressions in the UI? IMO doing it within the data-model is much more suitable.

- Marcus

[vinieme12 · Accepted solution]

You can using Valueloop() as below

temp:
load * inline [
PersonalNumber, DateChange, Category
11111,20220101,3
11111,20220401,4
11111,20220701,6
];

Create Two Variables

vListVal
=Concat(Distinct ValueLoop($(=min(total DateChange)),$(=max(total DateChange))),',')


vExpVal
=Concat(Distinct 'sum({<DateChange={' & ValueLoop($(=min(total DateChange)),$(=max(total DateChange))) & '}>}Category)',',')

In Chart Usage:

Dimension

=ValueLoop($(=min(total DateChange)),$(=max(total DateChange)))

Measure

=Pick(Match(ValueLoop($(=min(total DateChange)),$(=max(total DateChange)))
,$(vListVal)
)
,$(vExpVal)
)