Filter selection issue with Multiple if statement

[deblina_rai]

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.

Thanks

in advance.

[Yousef_Amarneh]

can you share a screenshot from the chart/table 

[deblina_rai]

screenshot is already attached.

[Yousef_Amarneh · Accepted solution]

Since you have same set analysis for the 4 accounts and the only difference is the account itself (which the chart will handle it automatically if the dimension that you are using in the chart is "Account") then I think the below should word

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

[deblina_rai]

thanks a lot for help. It worked.

[mahendrankr]

Hi Yousef,

I have similar issues. I am using the below expression in a line chart. I get the trend line if I select only one channel, however if i select multiple channels, the chart is simply blank. pl help me solve this.

if(Channel='AMS',Sum({$<Variables={"ams_direct_cost"}>}Values),
if(Channel='GCM',Sum({$<Variables={"gcm_direct_cost"}>}Values),
if(Channel='FB',Sum({$<Variables={"fb_direct_cost"}>}Values),
if(Channel='ISG',Sum({$<Variables={"isg_direct_cost"}>}Values)))))

I also tried pick match combo which also didn't yield any result. Can u pl help me fix this.

Pick(match('AMS','GCM','FB','ISG'),
Sum({$<Variables={"ams_direct_Impressions"}>}Values),
Sum({$<Variables={"gcm_direct_Impressions"}>}Values),
Sum({$<Variables={"fb_direct_Impressions"}>}Values),
Sum({$<Variables={"isg_direct_Impressions"}>}Values))

[manoranjan_d]

You can also try this solution

Sum({<Account=p(Account), [Period Date]={"$(=Max([Period Date]))"}, Period=, [Fiscal Month]=, [Fiscal Quarter]=, [Fiscal Year]=>} [Amount])