How to filter a measure by another field in different table?

[#1]

Hi,

I have 2 sheets, the first one shows everything but the second ones measures has to be filtered by a specific text field.

Can this filtration be done in an expression? something like:

= [measure] where [textField] = '01'

or in my context:

= "FactBudget[EtemadAmount]" where "DimAccount[ClassID]" = '01'

Or i have to filter in the script?

In the script i can't filter by the text field because it's in a different table than the measures table (Star Schema) which are joined in the diagram

The script is:

Main Tab:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
//SET QvdPath = "E:\QlikView\Final Dashboards\Source Data\QVD\Student\";
SET QvdPath = "D:\BudgetTables\";

OLEDB CONNECT32 TO [Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DWH_FIN_Budget;Data Source=hesssrv1];

//Totals:
DimAccount:
SQL
evaluate('dimaccount');
STORE DimAccount into $(QvdPath)DimAccount.qvd;
drop table DimAccount;


DimBudgetDate:
SQL
evaluate('DimBudgetDate');
STORE DimBudgetDate into $(QvdPath)DimBudgetDate.qvd;
drop table DimBudgetDate;


FactBudget:
SQL
evaluate('FactBudget');
STORE FactBudget into $(QvdPath)FactBudget.qvd;
drop table FactBudget;

Totals Tab:

//الحالة المالية اجمالي

//------------Account Dimension--------------
'Account Dimension':
LOAD "DimAccount[AccountKey]" as AccountKey,
     "DimAccount[AccountAlternativeKey]",
     "DimAccount[ClassID]" as 'رقم الباب',
     "DimAccount[ClassText]" as 'مسمى الباب',
     "DimAccount[ItemID]" as 'رقم البند',
     "DimAccount[ItemText]" as 'مسمى البند',
     "DimAccount[SerialID]" as 'المسلسل',
     "DimAccount[SerialText]" as 'مسمى المسلسل',
     "DimAccount[SiteID]" as 'رقم الجهة',
     "DimAccount[SiteText]" as 'مسمى الجهة',
     "DimAccount[CostCenterID]" as 'رقم مركز التكلفة',
     "DimAccount[CostCenterText]" as 'مسمى مركز التكلفة',
     "DimAccount[BeneficiarySiteID]",
     "DimAccount[BeneficiarySiteText]",
     "DimAccount[SuperVisorSiteID]",
     "DimAccount[SuperVisorSiteText]"
FROM
[$(QvdPath)DimAccount.qvd]
(qvd);

//-------------Date Dimension Table----------
'Date Dimension':
LOAD "DimBudgetDate[DateKey]" as DateKey,
     "DimBudgetDate[FiscalYear]" as 'السنة المالية'
FROM
[$(QvdPath)DimBudgetDate.qvd]
(qvd)
Where "DimBudgetDate[FiscalYear]" >= 2007 ;


//-------------Budget Fact Table-------------

FactBudgetTemp:
LOAD max("FactBudget[RequestKey]") as MaxRequest;
LOAD "FactBudget[RequestKey]"
FROM
[$(QvdPath)FactBudget.qvd]
(qvd);

let vMaxRequest = FieldValue('MaxRequest',1);
//let vMaxRequest = Peek('MaxRequest',0,'FactBudgetTemp');
Drop Table FactBudgetTemp;

'Budget Fact':
LOAD "FactBudget[DateKey]" as DateKey,
     "FactBudget[RequestKey]" as RequestKey,
     "FactBudget[AccountKey]" as AccountKey,
     "FactBudget[EtemadAmount]" as 'الاعتماد',
     "FactBudget[FromAmount]" as 'من',


'Budget Fact':
LOAD "FactBudget[DateKey]" as DateKey,
     "FactBudget[RequestKey]" as RequestKey,
     "FactBudget[AccountKey]" as AccountKey,
     "FactBudget[EtemadAmount]" as 'الاعتماد',
     "FactBudget[FromAmount]" as 'من',
     "FactBudget[ToAmount]" as 'إلى',
     "FactBudget[EtemadAfterAmount]" as 'الاعتماد بعد التعديل',
     "FactBudget[DirectConsumeAmount]" as 'المنصرف المباشر',
     "FactBudget[ErtebatAmount]" as 'الارتباط',
     "FactBudget[ConsumedErtebatAmount]" as 'المنصرف من الارتباط',
     "FactBudget[RemErtebatAmount]" as 'المتبقي من الارتباط',
     "FactBudget[TaklofaAmount]" as 'التكلفة',
     "FactBudget[ConsumedTaklofaAmount]" as 'المنصرف من التكلفة',
     "FactBudget[RemTaklofaAmount]" as 'المتبقي من التكلفة',
     "FactBudget[ConsumedAllClasses]" as 'المنصرف لكل الابواب'

FROM
[$(QvdPath)FactBudget.qvd]
(qvd)
WHERE "FactBudget[RequestKey]" = $(vMaxRequest);

Table Viewer:

[ashfaq_haseeb · Accepted solution]

Hi,

Try something like this in your expression.

sum({<[ClassID]={01}>}[EtemadAmount])

Regards

ASHFAQ