cancel
Showing results for
Did you mean:
Frequent Visitor

## Comparing values in a column to a measure by groups

Hi Community,

I'm trying to compare values for a column and a range of dates to a measure that calculates several cutoffs by those dates for the given cutoff, and then indicate in a measure or column those values that are greater than the cutoff.

My problem is that when comparing the column to the measure, rather than grouping and indicating for the groups, my code is indicating based upon the max/min of the cutoff for the column.

Here are some of my attempts to perform the above:

Highlight column = if('Tbl1'[output column]>'tbl1'[Tier cutoff measure], 1,0)

Tier cutoff measure=
calculate(minx('tb1l','tbl1'[output]),filter('tbl1','tbl1'[Tier cutoff rule column]=1),allexcept('tbl1','Tbl1'[Property Name],'Tbl1'[Date]),groupby('Tbl1','Tbl1'[output],'Tbl1'[Date],'Tbl1'[Property Name]),all('Tbl1'))

Tier cutoff rule column = if(and(calculate(AVERAGE('Tbl1'[DW/CW]),ALLEXCEPT('Tbl1','Tbl1'[Date],'Tbl1'[output],'Tbl1'[Property Name]))<[Rate],calculate(AVERAGE('Tbl1'[DW/CW]),allexcept('Tbl1','Tbl1'[Date],'Tbl1'[output],'Tbl1'[Property Name]))<>0),1,0)

Any help would be appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Comparing values in a column to a measure by groups

Hi Xiaoxin & Ashish,

Thank you for looking at the question!

I was able to figure out the problem I posed.  Essentially, I had to remove the all statement in the measure and replace with : filter('tbl1',,'tbl1'[Date]=earlier('tbl1'[Date])&&'tbl1'[Property Name]=earlier('Tbl1'[Property Name]) giving the following measure:

Tier cutoff measure=
calculate(minx('tb1l','tbl1'[output]),filter('tbl1','tbl1'[Tier cutoff rule column]=1),allexcept('tbl1','Tbl1'[Property Name],'Tbl1'[Date]),groupby('Tbl1','Tbl1'[output],'Tbl1'[Date],'Tbl1'[Property Name]),filter('tbl1',,'tbl1'[Date]=earlier('tbl1'[Date])&&'tbl1'[Property Name]=earlier('Tbl1'[Property Name]))

Thanks again!

3 REPLIES 3
Community Support Team

## Re: Comparing values in a column to a measure by groups

HI @jornelas,

Current you can't direct use measure in calculated column, dynamic measure result will be fixed as static value when you used in calculated column.

If you can please share some sample data with expected result to help us clarify your scenario and test to coding formula.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Super User

## Re: Comparing values in a column to a measure by groups

Hi,

You are a lot more likely to get valuable help if you describe the question, share some data and show the expected result.

Frequent Visitor

## Re: Comparing values in a column to a measure by groups

Hi Xiaoxin & Ashish,

Thank you for looking at the question!

I was able to figure out the problem I posed.  Essentially, I had to remove the all statement in the measure and replace with : filter('tbl1',,'tbl1'[Date]=earlier('tbl1'[Date])&&'tbl1'[Property Name]=earlier('Tbl1'[Property Name]) giving the following measure:

Tier cutoff measure=
calculate(minx('tb1l','tbl1'[output]),filter('tbl1','tbl1'[Tier cutoff rule column]=1),allexcept('tbl1','Tbl1'[Property Name],'Tbl1'[Date]),groupby('Tbl1','Tbl1'[output],'Tbl1'[Date],'Tbl1'[Property Name]),filter('tbl1',,'tbl1'[Date]=earlier('tbl1'[Date])&&'tbl1'[Property Name]=earlier('Tbl1'[Property Name]))

Thanks again!