Dynamic Set Analysis or If clause possibility

[sangeeth8787]

Just want to check how to re-write the following code in workable way

=sum({<DimensionTable1.ColumnA= Facttable.ColumnA, DimensonTable1.Column = {'2019'}>}Sales)

In Pivot chart, Instead of DimensionTable1.ColumnA, I need to use Facttable.ColumnA due to user requirement. 

Thank you

[sunny_talwar]

May be this

=Sum({<DimensonTable1.Column = {'2019'}>}
   If(DimensionTable1.ColumnA = Facttable.ColumnA, Sales)
)

[sangeeth8787]

Hi,

I am glad for your message, unfortunately it didn't worked for me, 

If I use calculated dimension  

If(isnull(Facttable.ColumnA),DimensionTable.Column,Facttable.ColumnA)

Expression

sum({<DimensionTable2.ColumnB = {'2019'}>})

Then it is giving me correct answer, but I am not allowed to use Calculated Dimension @ my work place. 

Can you we convert the above calculated dimension to Expression where I need to use Facttable.ColumnA as dimension column in Used Dimensions Section

[sunny_talwar]

Might be helpful if you are able to share a sample with the output you are expecting to see

[sangeeth8787]

Hi,

I am glad for your message, Please find the attached pic for example scenario.

[sunny_talwar]

at least share the Excel itself instead of sharing an image of an Excel file.

[sangeeth8787]

Hi,

Please find the excel attachment.

Many Thanks

[sunny_talwar]

May be try this

QUALIFY *;
UNQUALIFY ProductKey;

FactTableA:
LOAD * INLINE [
    ProductKey, Organisation, SalesAmount, ProductCategory
    1, ABC, 200, Bikes
    2, BCA, 500, Accessories
    3, ABB, 800, Tubes
    0, ADD, 300, 
    0, ACC, 200, 
];

FactTableB:
LOAD * INLINE [
    ProductKey, SalesAmount
    1, 100
    2, 1100
    3, 600
];

Dimension:
LOAD * INLINE [
    ProductKey, ProductCategory
    1, Bikes
    2, Accessories
    3, Tubes
    0, -
];

UNQUALIFY *;

Dim:
LOAD * INLINE [
    Dim
    1
    2
];

and then create a chart like this

Dimension

=Pick(Dim, Dimension.ProductCategory, FactTableA.Organisation)

Expressions

Pick(Dim, Sum({<Dimension.ProductCategory -= {'-'}>}FactTableA.SalesAmount), Sum({<Dimension.ProductCategory = {'-'}>}FactTableA.SalesAmount))

and

Pick(Dim, Sum(FactTableB.SalesAmount))

Not sure how much improvement it will give, but give it a shot

[sangeeth8787]

Hi,

I am glad for your message, If I am using the following expression in dimensions tab, the performance turning out be very poor.
=Pick(Dim, Dimension.ProductCategory, FactTableA.Organisation).

I believe it might be working by creating dummy values to dimension table and assign the same keys to dimension and fact tables.

[sangeeth8787 · Accepted solution]

Hi,

The following attachment might be works in such scenario's for any reporting platform.

Thanks