Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I need multiple if statements in a measure based on dimensions selected. For example if you pick from [Fiscal - Year], [Fiscal - Quarter Id], or [Fiscal - Period Id], the corresponding measures should be calculated. Im not sure what the syntax would be though.
=if(
GetSelectedCount([Fiscal - Year])>0,
round(Sum({$<[Fiscal - Year]={$(=Max([Fiscal - Year])-1)},[Filter - Date Context]={'Shipped'}>}[Amount - Shipped/Fulfilled USD])))
Or
=if(
GetSelectedCount([Fiscal - Quarter Id])>0,
round(Sum({$<[Fiscal - Quarter Id]={$(=Max([Fiscal - Quarter Id])-4)}>}[Amount - Shipped/Fulfilled USD])))
Or
=if(
GetSelectedCount([Fiscal - Period Id])>0,
round(Sum({$<[Fiscal - Period Id]={$(=Max([Fiscal - Period Id])-12)}>}[Amount - Shipped/Fulfilled USD])))
Thanks!
Dan
=if(
GetSelectedCount([Fiscal - Year])>0,
round(Sum({$<[Fiscal - Year]={$(=Max([Fiscal - Year])-1)},[Filter - Date Context]={'Shipped'}>}[Amount - Shipped/Fulfilled USD],
if(
GetSelectedCount([Fiscal - Quarter Id])>0,
round(Sum({$<[Fiscal - Quarter Id]={$(=Max([Fiscal - Quarter Id])-4)}>}[Amount - Shipped/Fulfilled USD],
if(
GetSelectedCount([Fiscal - Period Id])>0,
round(Sum({$<[Fiscal - Period Id]={$(=Max([Fiscal - Period Id])-12)}>}[Amount - Shipped/Fulfilled USD])))
Should work or you can use Pick(Match(),)
=if(
GetSelectedCount([Fiscal - Year])>0,
round(Sum({$<[Fiscal - Year]={$(=Max([Fiscal - Year])-1)},[Filter - Date Context]={'Shipped'}>}[Amount - Shipped/Fulfilled USD],
if(
GetSelectedCount([Fiscal - Quarter Id])>0,
round(Sum({$<[Fiscal - Quarter Id]={$(=Max([Fiscal - Quarter Id])-4)}>}[Amount - Shipped/Fulfilled USD],
if(
GetSelectedCount([Fiscal - Period Id])>0,
round(Sum({$<[Fiscal - Period Id]={$(=Max([Fiscal - Period Id])-12)}>}[Amount - Shipped/Fulfilled USD])))
How could you use Pick and Match in this example?
=Pick(
Match(GetSelectedCount([Fiscal - Year])>0,GetSelectedCount([Fiscal - Quarter Id])>0,GetSelectedCount([Fiscal - Period Id])>0
,
round(Sum({$<[Fiscal - Year]={$(=Max([Fiscal - Year])-1)},[Filter - Date Context]={'Shipped'}>}[Amount - Shipped/Fulfilled USD])),
round(Sum({$<[Fiscal - Quarter Id]={$(=Max([Fiscal - Quarter Id])-4)}>}[Amount - Shipped/Fulfilled USD])),
round(Sum({$<[Fiscal - Period Id]={$(=Max([Fiscal - Period Id])-12)}>}[Amount - Shipped/Fulfilled USD]))
)