LinkedIn
Twitter
Hi All,
I’m having difficulty creating a filter for a slice based on my design table.
It has 500 rows with 17 unique items in [main_asset_name]
The logic I’m trying to filter for is:
If no [last_change_date] exists for a group of [main_asset_name] then show the row where [source_asset_type] = "Cabinet"
otherwise
If one or more [last_change_date] exists for a group of [main_asset_name] then show only the row for the most recent [last_change_date]
The result I’m after is a list of the 17 unique [main_asset_name] showing:
[main_asset_name] with the most recent [last_change_date] or
[main_asset_name] with no [last_change_date] if the date doesn’t exist.
I’ve been going in circles all day even though I’ve come across excellent posts like:
FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), and SELECT() from @Steve and
Looking for an expression in a slice. I've go... from @Grant_Stead
Sample of design table below…
I’m sure this isn’t as difficult as I’m making it…? Thanks in advance… 
Solved
1 ACCEPTED SOLUTION
Sorry, This please
OR(
AND(IN(
[id],
SELECT(
rf_design[id],
AND(
[lcs]=[_THISROW].[lcs],
[main_asset_name]=[_THISROW].[main_asset_name],
[source_asset_type] = “Cabinet”,
ISBLANK([last_change_date])
))),
COUNT(SELECT(
rf_design[id],
AND(
[lcs]=[_THISROW].[lcs],
[main_asset_name]=[_THISROW].[main_asset_name],
[source_asset_type] = “Cabinet”,
ISNOTBLANK([last_change_date])
)))=0),
[id] =
MAXROW(
“rf_design”,
“last_change_date”,
AND(
[main_asset_name]=[_THISROW].[main_asset_name],
[lcs]=[_THISROW].[lcs]
)))
