Comparing values in a column to a measure by groups

[Anonymous]

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!

[Ashish_Mathur]

Hi,

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

[v-shex-msft]

HI @Anonymous,

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

[Anonymous · Accepted solution]

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!