Measure as a Filter

[imrasyed]

Hi Experts,

I have a two straight table with alternate states for comparison of IDs based on date selected.

First table has 15 dimensions.

Second table has 15 Dimension and 1 Measure(which is calculated).Measure is 

Count({<where date={first table selected date}>}IDs).I am getting 1's and  0's.So I replaced 1's with 'Exist' and 0's with 'Not Exist' text.It perfectly working but the requirement is the table should be filterable for Exist and not Exists value.

How can i provide a filterpane to user so that he should be able to filter 'Exists' and 'Not Exist' record.

Truly Appreciate your help

[SerhanKaraer]

Hello Imrasyed,

You can use aggr() function and turn measure into a dimension. You may have to specify all your 15 dimensions of second table as aggr(measure,dim1,dim2....dim15).

Regards,

[imrasyed]

if((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1)))=0
and only({$*<CreatedDateStart={"$(vMaxCreatedstart)"}>}[Scan Started At]) < '$(vMaxCreatedstart)','Scan Not Performed',
if((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1)))=0,'Fixed',
if((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1)))>0
and only({$*<CreatedDateEnd={"$(vMaxCreatedstart)"},key= {$(vG1Pids)}>}[Scan Started At]) < '$(vMaxCreatedstart)','Scan Not Performed',
if((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1)))>0,'Not Fixed'
))))

Here is my Measure Expression.

I am adding 0*sum().... get all the values of the selected date from first table to that of date in second table.

Please suggest me where should i be adding aggr funcion.

I did this

=if(aggr((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1))),key,CreatedDateEnd)=0
and aggr(only({$*<CreatedDateStart={"$(vMaxCreatedstart)"}>}[Scan Started At]),key,CreatedDateStart) < '$(vMaxCreatedstart)','Scan Not Performed',
if(aggr((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1))),key,CreatedDateEnd)=0,'Fixed',
if(aggr((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1))),key,CreatedDateEnd)>0
and aggr(only({$*<CreatedDateEnd={"$(vMaxCreatedstart)"},key= {$(vG1Pids)}>}[Scan Started At]),key,CreatedDateStart) < '$(vMaxCreatedstart)','Scan Not Performed',
if(aggr((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1))),key,CreatedDateEnd)>0,'Not Fixed'
))))

but i am getting only two values 'Not Fixed' and 'Scan Not Performed' the other value i.e 'Fixed' isn't coming.

Can you please have a look into Expression?