Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
I'm Managing support group as part of Incident Management and need to monitor KPIs of project.
Earlier I was able to create Measures, directly and easily using columns for "Closed Aged tickets" , "FiscalQrt" and "Fiscal Year"
Measure name "Closed Aged tickets" and DAX below
Measure name "FiscalQrt", and dax below
Measure name "Fiscal Year" and dax below
Now there is change in requirement and wanted to add few more Measures which count no of Closed_Aged_tickets greater than 18, 21, 25 days
I started working on PowerBI in the month of June and later updated powerBI with new releases July or Aug. Not sure, in which release this functionality stopped working.
please see the screenshots and errors:
Error: A single value for variaton 'Year' for column 'closed_at' in table 'incident' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Is there anyway alternative/ way to fix up this or do we have to inform MS PowerBI product group.
Thanks
Nawal
+91-9866755720
Solved! Go to Solution.
@Anonymous
Same error message is oberved in May/June 2017 version of Power BI Desktop. You formula can only be used in calculated column but not measure. To create a measure for Closed_Aged, you can use following formula.
Closed_Aged_Measure = CALCULATE ( COUNTROWS ( incident ), FILTER ( ALL ( incident ), DATEDIFF ( incident[sys_created_on], incident[resolved_at], DAY ) >= 15 ) )
Best Regards,
Herbert
@Anonymous
Same error message is oberved in May/June 2017 version of Power BI Desktop. You formula can only be used in calculated column but not measure. To create a measure for Closed_Aged, you can use following formula.
Closed_Aged_Measure = CALCULATE ( COUNTROWS ( incident ), FILTER ( ALL ( incident ), DATEDIFF ( incident[sys_created_on], incident[resolved_at], DAY ) >= 15 ) )
Best Regards,
Herbert
Hello Herbert,
Thank you for the response.
As I mentioned, In earlier version/update(May or June) I was able to create measure without any aggregation i.e. max or min or sumx or countx functions and got the count of aged tickets in my space directly as output in Card visual with simple DAX query.
Now its complicated (July or Aug update - I do not know exactly) and moreover, by using MAX function inside DateDiff, its not providing me the desired output by using CARD visual
please find the screenshots
old version of power BI when I created measure which is still working with card visual, which gives me count of how many tickets are aged
(eg:10 aged out of 100 tickets).
This is new version of powerbi which is not working with card visual
As suggested in many sessions of DAX, columns occupies the space on disk and measures does not.
Hence in my powerbi reporting I have create many Measures(like facial year an facial quarter as well) and its perfectly working.
I would like to request you to install May or June version/update of powerbi_32bit.msi and request you to test the given code for closed_aged, facial year and facial quarter(used in column charts to drill down years>quarters), which not working on July or Aug update.
Fiscal Year =
CONCATENATE("FY", IF(incident[closed_at].[MonthNo] <=6, VALUE(FORMAT(incident[closed_at],"YY")), VALUE(FORMAT(incident[closed_at],"YY")) +1))
FiscalQrt = IF((incident[closed_at].[MonthNo]>=1 && incident[closed_at].[MonthNo]<4),"FY "&incident[closed_at].[Year]&" Q3",
IF((incident[closed_at].[MonthNo]>=4 && incident[closed_at].[MonthNo]<7),"FY "&incident[closed_at].[Year]&" Q4",
IF((incident[closed_at].[MonthNo]>=7 && incident[closed_at].[MonthNo]<10),"FY "&incident[closed_at].[Year]+1&" Q1","FY "&incident[closed_at].[Year]+1&" Q2")))
If this is the case then I feel with the new release in July or Aug restricting & limiting the Power of Measures to use it freely(on-the-fly) and suppressing the ability of working with measures and forcing users to create unnecessary calculated columns(such as above and dim calenders tables) for creating and using measures on top of calculated columns which consumes space and reduces the performance of Power BI Users Experience in cloud.
Thanks
Nawal
v-nawsan
@Anonymous
It does not seem to be an issue. As the error message says, when you create a measure formula, if there are many values in the referred column, a single value cannot be determined. Then the calculation will be invalid. We need to specifying an aggregation.
This article should be helpful to understand it: https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
I am also giving a simple sample like below. We can create a column without specifying an aggregation. Because there is only one value for each row.
But we cannot use the same formula to create a measure. Because there are multiple values. We need to use an aggregation function.
Best Regards,
Herbert
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |