Measure to a dimension to filter

[RJN1135]

Hey everyone,

I'm trying to figure out how to use a calculated measure as a filter.

I have the following items:

Product ID (text), Customer(text), Month (Date), Sales(#), Forecast (#)

In a table, I've calculated a average measure by taking the sum of sales over three months and / 3, simple.  Then I calculated an accuracy % = Forecast / Sales Average = %.

I've been able to create another measure that takes the calculations (simplified expression below):

if(..(%) >1.25 , 'high', if....(%)<.8, 'low', 'good')

My measures column comes out with the correct High, Low, Good association to each Product ID Customer line.

How can I get take the High, Low, Good outcomes and make them a filter so I can filter down to only the Products /Customers with a Good %, or High %, etc.?

I need to convert this measure and its output to a dimension that I can use as a filter.

Thanks, 

[jbhappysocks]

Hi @RJN1135 

You can't create a calculated dimensions based on result of measures, as the measures requires the dimensions to be defined first.

One option is to aggregate this in you script, if you don't have a lot of dimensions in your data this is pretty doable, but might be a bit tricky with your rolling measures.

Another way of doing it is loading a dimension in your script with your ranges, don't connect it to your fact table.

load * Inline
[Result
OK
Good
Not Good];

Add the dimension to your table and then use pick match as in this example to define what line to display. This way instead of letting the calculation decide the dimension the dimension decides what calculation to display. 

pick(match(Result,'OK','Good','Not Good'),

if(Sum(Sales)/sum(Forecast)<=1.1 and Sum(Sales)/sum(Forecast)>=0.9, Sum(Sales)),

if(Sum(Sales)/sum(Forecast)>1.1,Sum(Sales)),

if(Sum(Sales)/sum(Forecast)<0.9,Sum(Sales)))

With my very simple example data I get this output. Since "Result" is not connected to Fact you can use dimensions pretty flexible. If you select "Good" you will see all lines that are "Good" . But if you select Customer A and B the Result in the second table will change to "OK" for both lines as those customers reach a lower accuracy together, this would be more difficult to do with a pre calculated dimension.

I have found this way of "cheating" with the dimensions pretty handy.

Edit: I put it a bit wrong, actually youcan create a calculated dimension using aggr. But it's normally not a good option if you want to use it as a filter since it doesn't really create a new dimension, it just looks like it does

This will look correct.

=Aggr(
if(sum(Sales)/sum(Forecast)>1.1,'Good',
if(sum(Sales)/sum(Forecast)<0.9,'Not Good',
'OK'))
,[Product ID],Customer)

But when you make selections you will not select on your created dimension, you will select the dimensions you aggregated your new dimension on:

After confirming "Good" selection your user will see this, and ask you why!?

So yes, you can create dimensions before you have your measures, but to use is as filters is normally not a good idea.