topic Re: applying logic based on the dimension values in the table in App Development

applying logic based on the dimension values in the table

taha_mansoor — Mon, 20 Dec 2021 21:02:46 GMT

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.

Re: applying logic based on the dimension values in the table

Anil_Babu_Samineni — Sun, 07 Mar 2021 08:00:50 GMT

Perhaps this

Sum(Value)/(Max(Month)-1)

Re: applying logic based on the dimension values in the table

taha_mansoor — Sun, 07 Mar 2021 08:06:59 GMT

@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.

Re: applying logic based on the dimension values in the table

Anil_Babu_Samineni — Sun, 07 Mar 2021 16:41:03 GMT

You mean this?

Sum({<Year={$(=Min({1}Year))}>} Value)/(Max({<Year={$(=Min({1}Year))}Month)-1)