Forum Discussion
Simple Measure going Blank when context applied
Circling back to provide a solution. (Thank you ChatGPT).
The issue for me came down to overlaying a visual filter which did not have a relationship with the variable "lastweek" which was on a date table. I was able to get the solution, by breaking down the measure and filtering and stipulating that the filters were to only occur between specific tables as per the below syntax in blue.
VAR Utilisation = CALCULATE( DIVIDE( CALCULATE(SUM(FactUtilisation[Chargeable Hours]),DimDate[Fiscal Week] = lastweek),
CALCULATE(SUM(FactUtilisation[Total hours]), DimDate[Fiscal Week] = lastweek) ),
FactUtilisation[employee] IN VALUES(FHTeam[employee]), DimDate[Fiscal Week] = lastweek )
RETURN Utilisation
ChatGPT explains the key difference better here:- FactUtilisation[employee] IN VALUES(FHTeam[employee])
This line of code creates a filter on the FactUtilisation table, specifically on the [employee] column. The filter is based on the selected values in the FHTeam[employee] column, which are passed as a parameter to the VALUES function. The VALUES function returns a table of unique values from the FHTeam[employee] column, which are then used to filter the FactUtilisation table. The IN operator is used to check whether the [employee] value for each row in the FactUtilisation table is in the table returned by the VALUES function. This effectively filters the FactUtilisation table to include only rows where the [employee] value matches one of the selected values in the FHTeam[employee] column.
Hi Anonymous,
I modify your formula to add 'in' operator and 'values' function, then it can calculate if the row content aggregate multiple records. You can try to use the following measure formula if it suitable for your requirement:
Utilisation PTD =
VAR Utilisation =
CALCULATE (
DIVIDE ( SUM ( FactHours[Chargeable Hours] ), SUM ( FactHours[Total hours] ) )
)
VAR FiscalPeriod =
CALCULATETABLE (
VALUES ( DimDate[Fiscal Period] ),
FILTER (
ALLSELECTED ( DimDate ),
DimDate[Date] IN VALUES ( 'Date Last Refreshed'[Date Last Refreshed] )
)
)
VAR Result =
CALCULATE (
[Utilisation],
FILTER ( ALLSELECTED ( DimDate ), DimDate[Fiscal Period] IN FiscalPeriod )
)
RETURN
ResultIf the above also not help, can you please share some dummy data that keep the raw data structure with expected results? They will help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Anonymous I have progressed and drilled down to one variable cuasing issues which I hope you can provide some advise on. Below I have the variable lastweek which uses a report refresh date, and looks up the fiscal week of 1 week earlier. This is then used to filter the fact table to give me the utilisation measure. Note that this can be done very easily with just a visual table so all relationships are correct.
Where there is an issue in the measure, is the VAR Lastweek below is = to 36. Which is correct. But it will not filter the Utilisation Calculation. If I change the "lastweek" in the filter calculation to the number 36 like this;
the data type is Integer so no conflict in types etc. Strange that lastweek = 36 and 36 as a number entered works but the variable won't?
Appreciate any help.