Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unable to create measures, which I was able to created directly on columns before new update.

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 

Spoiler
Closed_Aged = IF((DATEDIFF(incident[sys_created_on],incident[resolved_at],DAY))>=15,1,0)

Dax_closed_aged.PNG

 

 

 

 

 

 

 

 

 

 

 

 

Measure name "FiscalQrt",  and dax below 

Spoiler
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")))

Dax_Facial_Qrt.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Measure name "Fiscal Year" and dax below 

Spoiler
Fiscal Year =
CONCATENATE("FY", IF(incident[closed_at].[MonthNo] <=6, VALUE(FORMAT(incident[closed_at],"YY")), VALUE(FORMAT(incident[closed_at],"YY")) +1)) 

Dax_Facial_Year.PNG

 

 

 

 

 

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:

 

Dax_closed_aged_error.PNG

Spoiler
Error: A single value for column 'sys_created_on' 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.

 

Dax_Facial_Qrt_error.PNG

 

 

 

 

Spoiler

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

1 ACCEPTED SOLUTION
v-haibl-msft
Employee
Employee

@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

View solution in original post

3 REPLIES 3
v-haibl-msft
Employee
Employee

@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
Not applicable

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).

 

Nawal_Measure_for_count.PNG

 

This is new version of powerbi which is not working with card visual

 

Solution_by_Herbert.PNG

 

 

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

v-haibl-msft
Employee
Employee

@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.

Unable to create measures, which I was able to created directly on columns before new update._1.jpg

 

But we cannot use the same formula to create a measure. Because there are multiple values. We need to use an aggregation function.

Unable to create measures, which I was able to created directly on columns before new update._2.jpg

 

Best Regards,
Herbert

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.