Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

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