prevent calc. dimensions in kpi data island

[#1]

Hi all,

Since calculated dimensions are bad for performance i would like to not use them anymore.

I have a complex datamodel, and we have one dashboard containing 8 pivot tables with 6 expressions. (e.g. HR, Finance, risks), by making selections in a cost center (or client or employee) box they can investigate the performance. The 48 kpi are read in via an excel file (data island)

requirement: all 6 expressions must be visible (so suppres zeroes won't work, but I also don't understand the suppress null, which does not change any behaviour)

Question, how can I remove my calc. dimension and only show the 6 relevant kpi's in each box without using a cal. dimension?

Dimension: =if(Block =1,[KPI name])

expression: if([Tech Name] = 13, count( something))

thanks a lot!

full expression for background info:

if([Tech Name] = 13, count({< eng_costcenter_key = {$(vCostCenter)},EA.type={'EA'}, EA.ass_start_month = {'$(=date($(vReportingDate)))'}>}  EA.assessmentId),

if([Tech Name] = 14, count({< eng_costcenter_key = {$(vCostCenter)},EA.type={'EC'}, EA.ass_start_month = {'$(=date($(vReportingDate)))'}>}  EA.assessmentId),

if([Tech Name] = 16, count({< eng_costcenter_key = {$(vCostCenter)},Z_interne_code_flag = {'1'}, cons_ingediend_month = {'$(=date($(vReportingDate)))'}>} consultId)

etc....

[johnw · Accepted solution]

If Block is on the same table as KPI name, you could just create a new field:

,if(Block=1,[KPI name]) as [Block 1 KPI name]

Then use that in the chart.

[#3]

Thank you.

They are in the same table.

Tech Name,  KPI name

11,               Te accepteren klanten

12                Te continueren klanten

13                Te accepteren opdrachten

...

60

E.g. first kpi box should only show numbers 11, 12 and 13

second box 21, 22, 23

Problem is, That suppress zeroes removes also correct kpi's (from the box) which count 0, If i remove the suppress I also get the other kpi's with - symbols in the expression. (because no expression if off course defined for them in the box.)

Thank you!

[johnw]

Continuing on what I'd said earlier, you'd use [Block 1 KPI name] as the dimension for one of your charts, and presumably have other charts with [Block 2 KPI name] and so forth.  You would suppress the dimension when null, not suppress zeros for the KPIs themselves. 

But I'm really not sure I understand what you're doing and what the problem is.

[#5]

Hello Martijn,

If I understood this correctly you were making an effort to build a scorecard with custom KPI list? Can you share more details, how did it go, what was your plan to map measure to "KPI Name... row" from excel data?

I wasn able to find any "live" examples of similar.

Thank you and best regards,