Set Analysis Aggr does not work in if statement

[Kat92]

Hi,

I have created the following calculation for a report that calculates the Working Days per Month and sums up for all months and Users succesfully

Sum(Aggr(avg(WD), Year_Month, [User]))

I wanted to create a measure to show together in a table multiple KPIs and display each one in a row of the table and this is why I created the below, but my calculation brings always 0 in the outcome of the if statement.

if(Valuelist('Measure1','# Business Days'
) = '# Business Days', Sum(Aggr(avg(WD), Year_Month, [User])), [Measure1])

Could someone assist?

Thanks in advance

[jochem_zw]

try this:

Dimension (Calculated dimension)

Valuelist('Measure1','# Business Days')

Expression:

=pick(match(Valuelist('Measure1','# Business Days'),'Measure1','# Business Days')
   ,Sum(Aggr(avg(WD), Year_Month, [User]))
   ,[Measure1])

[Jebrezov]

Do you have sample data so we can get a better idea of the numbers/fields/what is being done? In place of the if true and else sections of the if statement, you can force the values to be 1 and 2 or something and verify the conditional check is returning true or false correctly. This way you can see which of the two expressions are evaluating to 0 (I’m not sure if it’s the sum(agg()) expression or the measure1)

[Kat92]

Thank you Jochem. I have tried the pick function as suggested and seems that with current structure it brings the correct Value to the Measure1 place and I did the minor change below (just to be on the correct order

=pick(match(Valuelist('Measure1','# Business Days'),'Measure1','# Business Days')
   ,Sum(Aggr(avg(WD), Year_Month, [User]))
   ,[Measure1],Sum(Aggr(avg(WD), Year_Month, [User]))

However, in this case again my calculation shows 0 always, while in the first place it was correct but under the wrong label.

Not sure how this is working

[Jebrezov]

you should just need to flip the match values as shown below

=pick(match(Valuelist('Measure1','# Business Days'),'# Business Days','Measure1')
   ,Sum(Aggr(avg(WD), Year_Month, [User]))
   ,[Measure1])

what this is doing is matching the valuelist value with the 2 options #business days and measure1. match returns the position of the match in the list (in this case 1 for # business days and 2 for measure1. pick then uses that number to perform the equation in that position (in this case 1 will result in the sum(aggr()) and 2 will result in measure1. both the match and pick functions can be extended further to include 3,4,5, etc... options if this ever needs to grow.