Filter not working on a measure

[Marmaduke0703]

Hi all!

I have an issue where a filter isn’t appearing to have any impact on a table/measure and I’m not sure how to fix it. It feels like this should be a simple measure…

Unfortunately I’m not able to share the dataset so I’ll explain as best as I can ☹

Average completion % =
calculate(
COUNT('Percentage Report Log'[Completion percentage]),
filter('Percentage Report Log',
'Percentage Report Log'[Updated] = [Last Updated] && 'Percentage Report Log'[Key] = related('Manual Updates'[Ref.]))
)

** using count in place of average initially to check it is filtering as expected

For context, I’m trying to work out the average completion % by a group as specified in another column (“Release version”).
The completion % itself has been added to the ‘Percentage Report Log’ as a merged query from a separate table (I have also tried writing as a lookup measure but it didn’t help here).

The ‘Percentage Log Report’ is a table that stores information on a number of tickets/ref numbers and has a unique ID created using the ticket number and the date updated (tickets have multiple updates, hence the need to match on [Last Updated]). I’ve tested the filter with simple text equalities and it’s fine. I’ve then tested both parts of the current filter separately and neither have any effect (combined or individually).

• [Last Update] is a measure that returns the latest date/time value in the ‘Updated’ column and is dynamic based on a selected date – this works when displaying the result in a matrix and as part of other Lookup functions in other measures but has no effect in this filter.
• The Key / Ref. equality criteria has also been used in other measures and works fine there but has no effect here. There is a one (Ref.) to many (Key) relationship between these fields.

Any thoughts or tips would be greatly appreciated! Even just understanding why it’s failing will help

 

[GraH]

Are you using dimensions from the 'many side' inside your pivot co-ordinated fields, so as row or column header?

 

[Marmaduke0703]

Are you using dimensions from the 'many side' inside your pivot co-ordinated fields, so as row or column header?

Nothing directly visible I'm afraid. The table I'm creating has two fields only - the “Release version” (a column from the Manual updates table, the 'one' side of the relationship) and the measure that isn't working.

The most important part is around the date though - I'm wondering if there's some kind of circular reference? Below are all the measures that link and feed it. Could there be an issue using the 'Percentage Report Log'[Updated] column in both the filter part and the average calculation?

Average completion =
calculate(
count('Percentage Report Log'[Completion percentage]),
filter('Percentage Report Log',
'Percentage Report Log'[Updated] = [Last Updated] ))

Last Updated =
// maximum time on selected date or closest previous date, returning a text hold space for Manual Refs not in Percentage Report Log
if(
isblank(
calculate(
max('Percentage Report Log'[Updated]),
filter('Percentage Report Log', 'Percentage Report Log'[Last Update] <= [Selected Date]))),
"-",
calculate(
max('Percentage Report Log'[Updated]),
filter('Percentage Report Log', 'Percentage Report Log'[Last Update] <= [Selected Date])))


Selected Date =
// Date table not related to either Manual update or Percentage Report Log, just used as reference. Uses selection or today's date.
if(
HASONEVALUE('Date Table'[Date]),
max('Date Table'[Date]),
TODAY())



Thanks, appreciate your help on this!

 

[Marmaduke0703]

Took a while but I worked it out.

On further testing, the date filter was causing a circular reference, the key/ref relationship was fine and doing its thing as it should.

Option 1 - create a duplicate table and reference that in the final formula. It did the trick but not ideal...

Option 2 - New formula as below:

Average Completion =
AVERAGEX(
SUMMARIZE(
'Percentage Report Log',
'Percentage Report Log'[Key],
"completion %", calculate(
LOOKUPVALUE('Percentage Report Log'[Completion], 'Percentage Report Log'[Updated], [Last Updated]),
FILTER('Percentage Report Log', 'Percentage Report Log'[Key] = related ('Manual Updates'[Ref.])))),

[completion %])