Forum Discussion
Simple Measure going Blank when context applied
- Anonymous3 years ago
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.
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.