Simple Measure going Blank when context applied

[Anonymous]

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 team level

3) (Date Table) Standard with fiscal weeks and periods- relationship with work date in fact table

4) (Date Last Refreshed) table with the date the report was alst refreshed.

Objective, of my measure is to Calculate a % of Billable hours worked on the Fact Table, where the Fiscal Period = Period of last refresh date, and apply this measure to the employees in the team table (dimension table linked to the Hours Table).

The current measure is:

Utilisation PTD =

var Utilisation = CALCULATE(DIVIDE(sum(FactHours[Chargeable Hours]),sum(FactHours[Total hours])))

var FiscalPeriod = LOOKUPVALUE(DimDate[Fiscal Period],DimDate[Date],MAX('Date Last Refreshed'[Date Last Refreshed]))

var Result = CALCULATE([Utilisation], FILTER(DimDate, DimDate[Fiscal Period] = FiscalPeriod))

Each of the Vairables work independently, and the entire calculation works as a single number in a table.

However when I then add the Team table to show the employees I want to apply this to, I get a blank.  So the measure won't calculate the utilisation by period when filtered by an employee.

A relationship exists between the Team table and Fact table so no issue there.  And I can create the result by building a table visual with Period filtered to Period 8, Team Employees "All but Blank" and adding a utilisation measure, however combined as a measure it won't work.  No other filters exist on the table

I am sure there is a minor change but unsure where to go from here.

Sorry no PBX file due to confidentiality

[Anonymous · Accepted solution]

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.

[Anonymous]

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
    Result

If 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]

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

VAR Utilisation = CALCULATE(

DIVIDE(

CALCULATE(SUM(FactUtilisation[Chargeable Hours]),DimDate[Fiscal Week] = 36),

CALCULATE(SUM(FactUtilisation[Total hours]), DimDate[Fiscal Week] = 36

)

))

it works.  For some reason the VAR lastweek value is not being accepted as a value for a filter?

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.

Utilisation LastFiscal Week =

VAR lastweek = VALUE(LOOKUPVALUE(DimDate[Fiscal Week],

DimDate[Date],

MAX('Last Refreshed'[Date Last Refreshed])

)) - 1

VAR fiscalyear = LOOKUPVALUE(

DimDate[Fiscal Year],

DimDate[Date],

MAX('Last Refreshed'[Date Last Refreshed])

)

VAR lastweekperiod = CALCULATE(

MAX(DimDate[Fiscal Period]),

FILTER(DimDate,DimDate[Fiscal Year] = fiscalyear),

FILTER(DimDate, DimDate[Fiscal Week] = lastweek)

)

VAR Utilisation = CALCULATE(

DIVIDE(

CALCULATE(SUM(FactUtilisation[Chargeable Hours]),DimDate[Fiscal Week] = lastweek),

CALCULATE(SUM(FactUtilisation[Total hours]), DimDate[Fiscal Week] = lastweek

)

))

RETURN Utilisation

[Anonymous]

Thank you Xiaoxin,  appreciate the help, unfortunately have been trying to get this going for a few days but still no joy.  I have an issue with it nor filtereing on a related table.  thank youf or the help.

[Anonymous]

Hi @Anonymous,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to troubleshoot.

How to Get Your Question Answered Quickly  

Notice: please remove the sensitive data before share.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin I am unable to provide a model, hence I was tryint o explain it.  I am revisiting the model to seee if I can get around my challenge.  Appreciate the help.  Can't see how to close this question though.  If I work it out I will return with the answer and post and close.