Use measures as name as dimension in filters

[Sunil645454]

I have around 12 measure and 10 dimension in my table and above that table I have filters. these 12 measures are divided like 7 and 5 so there will be 4 filters.

Filter1 -  suppose I have give 7 measure in my table and named as measure1 to measure 7 so I will have filter that would have these measure list from 1 to 7

Filter2 - have bucket of values that I have in my table like >5 Lakhs, 5-10 Lakh and till 25 Lakhs

Filter3 - have rest of the 5 measure and have name as Measure8 to measure12 names list

Filter4 - have again bucket from >5 Lakhs , 5-25 Lakhs and so on till >2 Crores

Now based on the selections my tables measure should reflect , like user select Filter 1 -measure 5 and Filter 2  5-10 Lakh then in my table all values should come between 5-10 Lakh only and same for 3rd and 4th filter whatever measure selected table measure should reflect according to that. FYI- All measure are different not same in filter 3 there will be another measure that should also reflect accordingly

[Sunil645454]

 Filters will be like screen shots , I have attached the qvf as well

Explanation:- Parameter 1 is selected as Discount and Bucket 1 has range of 1000-3000 also Parameter 2 has sales amount and Bucket2 is 10000-30000 so now table should show only those rows where these two bucket range is fall and discount and sales amount values will be in these bucket range only not less or more only in between of selection for both discount and sales amount as per their bucket range and rest column will be adjusted accordingly whatever the value is on those rows.

[rubenmarin]

Hi, I can give you directions.

In example load the selectors with additional fields to create the dynamic conditions, like:

Bucket1:
LOAD *
INLINE [
Bucket1		,Bucket1Min	,Bucket1Max
< 1000		,-999999999	,1000
1000 - 3000	,1000		,3000
3000 - 6000	,3000		,6000
6000 - 10000,6000		,10000
> 10000		,10000		,999999999
];

Measure_List:
LOAD *
INLINE [
Parameter1	,Parameter1Expression
Discount	,Sum(Discount)
Tax Amount	,Sum(Tax_Amount)
];

So you can use those fields to create expressions like:

If($(=Parameter1Expression)>=$(=Bucket1Min) and $(=Parameter1Expression)<$(=Bucket1Max)
,Sum(Unit_Price))

You will need to add the condition in each column to filter the rows.

[rubenmarin]

For the toals you can selt the option to 'Sum' instead of Auto, or use an aggr like:

Sum(Aggr([Expression], [TableDimension1],[TableDimension2]...))

[Sunil645454]

Hi, 

But how would I give my expression in inline load for Parameter1Expression?

[Sunil645454]

Can you please give me the working qvf that I shared?

[rubenmarin]

Hi @Sunil645454, here you have it. It's not completed but I think you can get the idea from here.

[Sunil645454]

I am not able to understand as how the measure list will work as the are inline load as per attachment , also its not working in front end, expression is in error and conditions are not working

[Sunil645454]

My this file is working fine now and this is what exactly I was looking for now the issue is (1) based on the filters discount has value 1000 but tax amount is 0 so in such cases 0 should come as orginal values that is 1800 once remove filter then see same for both the columns where when it is out of bucket area but one selected range is meeting the creteria in that case 0 value from other column should show its origional value to compare, 

2nd Issue - when selected filter does not meet the requirement and give 0 and 0 like last row then that row should not visible

Please look into it

[rubenmarin]

Hi @Sunil645454, sorry for delay, my last answer was given while multitasking and I didn't have time to complete and test.

The idea behind the variables is to create dynamic 'If' conditions. To make it work I set the conditions in another variable that returns the 'If' conditions based on the parameter selctions, I attach again the first example with the variable created, and now it's applied to all columns.

Maybe it needs some adjustements, like the Unit Price maybe needs an Avg instead of a Sum or something else I could miss.

The vCheckCoditions variable is:

=If(GetSelectedCount(Parameter1) = 0 and GetSelectedCount(Parameter2) = 0  
  ,'If(1=1'  
  ,If(GetSelectedCount(Parameter1) = 1 and GetSelectedCount(Parameter2) = 0    
    ,'If($(=Parameter1Expression)>=$(=Bucket1Min) and $(=Parameter1Expression)<$(=Bucket1Max)'    
    ,If(GetSelectedCount(Parameter1) = 0 and GetSelectedCount(Parameter2) = 1     
      ,'If($(=Parameter2Expression)>=$(=Bucket2Min) and $(=Parameter2Expression)<$(=Bucket2Max)'      
      ,'If($(=Parameter1Expression)>=$(=Bucket1Min) and $(=Parameter1Expression)<$(=Bucket1Max) and $(=Parameter2Expression)>=$(=Bucket2Min) and $(=Parameter2Expression)<$(=Bucket2Max)'
)))

And using this variable the expression of each column could be a simple:

$(vCheckConditions),[ColumnExpression])