Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a scenario whereby I need to put a logical condition in a measure expression based on the dimension values in the same table chart. For example, I have below data set in my data model:
Year | Month | Value |
2013 | 1 | 1000 |
2013 | 2 | 2000 |
2014 | 1 | 3000 |
2014 | 2 | 4000 |
I have to achieve a KPI in the straight/pivot chart whereby I need to apply a logic on first year rows(i.e. rows having year 2013). So, what I want to do in the expression is something like: if year=2013, Value/Month-1. So, the evaluation of this should be 1000/0, 2000/1. Here I am substracting 1 from each value of the column Month refering the Year column value 2013 only. How exactly I can write this in a straight/pivot chart expression having the dimensions Year and Month in chart as dimensions.
Thanks.
Perhaps this
Sum(Value)/(Max(Month)-1)
@Anil_Babu_Samineni thanks for the response but I need to get the logic on Min Year, so even I replace the Max with Min and Month with Year in your expression above, it will take the Year value based on app selections. What if Year has not been selected. I need to apply the logic irrespective of selections i.e. based on dimension value of each row of the straight/pivot table.
You mean this?
Sum({<Year={$(=Min({1}Year))}>} Value)/(Max({<Year={$(=Min({1}Year))}Month)-1)