Bad performance when coloring cells in pivot table

[#1]

Hi everyone,

I have a pivot table that is working OK, but when I try to apply a background color to the cells based based on a dimension value, it becomes slow and uses a lot of RAM(from 160 mb to 3.6 GB).

Has anyone had this issue before?

Please find attached my sample file. Right now the color expression is commented under the measure background color expression.

Thanks in advance.

Daniel

[#2]

same sample file(zip format).

[Gysbert_Wassenaar]

Try loading the colors in a seperate table:

Colors:

LOAD CategoryID as ID,

     rgb(R,G,B) as ColorRGB    

RESIDENT Category

Then use this expression to get the colors: FieldValue('ColorRGB', CategoryID)

[SergeyMak]

I Think you have some problem with data. Your fact table has almost 27 ml rows.

It looks like you don't have values in Account field in Fact table

Regards,

Sergey

[#5]

Hi Gysbert,

It seems to be working.

However, if I have repeated colors in my Colors table I get the wrong colors.

Is there a way to get the ColorRGB using the ID in the Colors table? The second parameter of FieldValue  is the value found in the position n(by load order) and if I have repeated values I wont get a one-to-one relationship between ID and ColorRGB.

Best regards,

Daniel

[Gysbert_Wassenaar]

No, for the fieldvalue function to work there should be a one-to-one relationship to ID and color. Otherwise you'll have to use something like the pick function: pick(CategoryID, $(=concat(ColorRGB , ',', ColorID)))

[#7]

Hi Gysbert,

Yes, I have a list of Ids and each Id has one color. Like this:

CategoryColors:

LOAD ColorID, RGB(R,G,B) as ColorRGB INLINE [

    ColorID, R, G, B

    1, 192,192,192

    2, 255, 255, 255

  3, 255, 255, 255

  4, 153, 204, 0

  5, 255, 255, 255

  6, 255, 255, 255

  7, 153, 204, 0

  8, 153, 204, 0           

  9, 255, 255, 255

  10, 255, 255, 255

  11, 255, 255, 255

];

However, If I try to lookup FieldValue('ColorRGB', 3), it returns RGB(153, 204, 0) instead of RGB(255, 255, 255).

Also, FieldValue('ColorRGB', 4) returns NULL. The FieldValue second parameter does not use the ColorID but the load order.

Best regards,

Daniel

[Gysbert_Wassenaar]

What you can do is add a ColorID field to the Category table:

Category:

LOAD *, autonumber(Color) as ColorID;

LOAD CategoryID,

     Category,

     R,

     G,

     B,

     rgb(R,G,B) as Color

FROM [Category.qvd] (qvd);

STORE Category into Category.qvd;

Colors:

LOAD ColorID as ID,

     rgb(R,G,B) as ColorRGB    

FROM [Category.qvd] (qvd);

Then you can use the fieldvalue function with ColorID as second parameter.