From measure to dimension

[simotrab]

Hi community!

I am facing this issue: I have created a measure that generates nominal results, in other words, letters.

Here an example:

load*inline

[date,customer,

29/10/2016,'a',

29/11/2016,'a'

29/12/2016,'a'

12/10/2016,'b',

13/10/2016,'b'

14/10/2016,'b'

]

;

I wanted to mark the customers that are not appearing in this way: generally customer x appear in average every day, it is 3 day that does not appear, this is a problem. Here we go:

The measure are these:

last appear, the last date of the customer :

max([date])

dayFromLast, day from the last appearance of the customer  (use 03/01/2017, DD/MM/YYYY):

(today()-max(date))

EDIT:

GeneralBehaviour, how often the customer appears (average):

(max([date])- min([date]))/(count(customer)-1)

EDIT:

risk, if the days of the customer's last appearance is bigger than the GeneralBehaviour (bad) or viceversa (good)

if((max([date])- min([date]))/(count(customer)-1)>(today()-max(date)),'good','bad')

My question is: could I have risk as dimension, so I can put it in a filter? If so, how? Logically putting it in a filter is not possible. I know that I can solve it adding a column in the data load, but I'd like to do it in the app without touching the data load.

Thanks in advance.

[sunny_talwar · Accepted solution]

1st a question for you.... Count(DISTINCT Customer) will always be 1, no? I mean Customer is a dimension, what is the point of dividing by DISTINCT Customer? May be you Want Count(Customer)

Coming back to your question, try this:

Aggr(If((Max({1}[date]) - Min({1}[date]))/Count({1}DISTINCT customer) > (Today() - Max({1}date)), 'good', 'bad'), customer)

[simotrab]

Hi! Thanks a lot, I have corrected the formulae, and your result works fine.