The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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!
User | Count |
---|---|
56 | |
54 | |
54 | |
49 | |
30 |
User | Count |
---|---|
173 | |
89 | |
70 | |
46 | |
45 |