Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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!
Hi,
You are a lot more likely to get valuable help if you describe the question, share some data and show the expected result.
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |