topic Filter selection issue with Multiple if statement in App Development

Filter selection issue with Multiple if statement

deblina_rai — Sat, 16 Nov 2024 19:50:14 GMT

I have written multiple if statements in two ways to create a master measure.

way 1:

pick(match(Account,'MTDAvgAUM','MTDEndAUM','YTDAvgAUM','YTDEndAUM'),Sum({<Account={'MTDAvgAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,Sum({<Account={'MTDEndAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,Sum({<Account={'YTDAvgAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,Sum({<Account={'YTDEndAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount]))

way 2:

Num(
If(Account='MTDAvgAUM', Sum({<Account={'MTDAvgAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,If(Account='MTDEndAUM', Sum({<Account={'MTDEndAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,If(Account='YTDAvgAUM', Sum({<Account={'YTDAvgAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])
,If(Account='YTDEndAUM', Sum({<Account={'YTDEndAUM'}, [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount]),
sum(Amount))))),'#,##0')

But problem is when i am selecting account =MTDAvgAUM in filter , for rest of the if clause it giving 0 which i dont want to show. but i dont want to uncheck include zero value option also.

I want to show only result for selected account.

Please give me some idea how to avoid zero values for rest of the values.

Thanks

in advance.

Re: Filter selection issue with Multiple if statement

_armoco_ — Wed, 23 Oct 2019 05:47:31 GMT

Hi,

Not sure if this will work for you but,

You can try using Getcurrentselection() function. See an example below which is working for me:

if(len(GetCurrentSelections(Filter))=0, Amt,

Pick(WildMatch(Filter,'1M', '2M', '3M'),
if(Amt>10 and Amt<=20, Amt,Null()),
if(Amt>20 and Amt<=30, Amt,Null()),
if(Amt>30, Amt,Null())
))

This expression will first check if the selected filter value is 0, so on no selection of filter it will show everything and on selection of filter, it will show only for selected. BUT YOU WILL NEED TO UNCHECK INCLUDE ZERO VALUE option

Check Sheet 3 in the attached file. See the difference by checking and unchecking include zero values.

Regards,

Arjun

Re: Filter selection issue with Multiple if statement

deblina_rai — Wed, 23 Oct 2019 06:05:09 GMT

As per my requirement I cant uncheck include zero value option.

I tried your option but if I select one value, zero or null are coming for rest of the clause which I don't want.

any other way please?