Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Simple Measure going Blank when context applied

I have a simple model with the following tables: 1) (Hours)- Fact Table with Staff, Billable Hours and Total Hours by Work Date 2) (Team) - Table with staff in a team- used to filter Fact table to ...
  • Anonymous's avatar
    Anonymous
    3 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:  

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