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.
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
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.
- Anonymous3 years agoNot applicable
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
- Anonymous3 years agoNot applicable
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.