Trouble with expression to filter a slice

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… :slight_smile:

Capture-design-table-example

Please try

OR(

IN([Table Key], SELECT (Table Name[Table Key], AND([main_asset_name]=[_THISROW].[main_asset_name], [source_asset_type] = “Cabinet”, ISBLANK([last_change_date]))) ),

[Table Key]=MAXROW(“Table Name”, “last_change_date”, [main_asset_name]=[_THISROW].[main_asset_name])
)

Hi @Suvrutt_Gurjar,

We meet again… Thank you for the expression… :slight_smile:

I currently have data for three locations in the rf_design table in [lcs] : D103, D107, N123

I’ve added [lcs]=[_THISROW].[lcs], to your expression but I’m getting odd results where sometimes the [id] row shows twice?

I’m busy investigating and will report back.

Many thanks… :+1:

OR(

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])
))),

[id] =
MAXROW(
“rf_design”,
“last_change_date”,
[main_asset_name]=[_THISROW].[main_asset_name]
))

Screenshot:

rf_design table extract:

I have not analyzed your latest post in detail, but as a quick try, does following expression work?

OR(

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])
))),

[id] =
MAXROW(
“rf_design”,
“last_change_date”,
AND([main_asset_name]=[_THISROW].[main_asset_name], [lcs]=[_THISROW].[lcs]
)))

Thanks for the very speedy reply!

Nope… It appears to show a few extra “duplicate” rows…?

OR(

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])
))),

[id] =
MAXROW(
“rf_design”,
“last_change_date”,
AND(
[main_asset_name]=[_THISROW].[main_asset_name],
[lcs]=[_THISROW].[lcs]
)))

Thank you.
This one ? :slight_smile:

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

I got this error:

Expression ‘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] )))’ could not be parsed due to exception: Number of opened and closed parentheses does not match.

Then removed the last parentheses and got this one:

Condition AND(IN([id],SELECT(RF_DESIGN[id],AND(([lcs] = [id].[lcs]), ([main_asset_name] = [id].[main_asset_name]), ([source_asset_type] = “Cabinet”), ISBLANK([last_change_date])))), COUNT(SELECT(RF_DESIGN[id],AND(([lcs] = [id].[lcs]), ([main_asset_name] = [id].[main_asset_name]), ([source_asset_type] = “Cabinet”), NOT(ISBLANK([last_change_date])))))) has an invalid structure: subexpressions must be Yes/No conditions

Sorry, This please :slight_smile:

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

1 Like

Hey @Suvrutt_Gurjar… It’s starting to look very beautiful…!!! :+1:

There is only one remaining “duplicate” for ZDC06

I’m busy looking at the data to see what’s “special” about just this one…?

…OK… So… I found the difference…

ZDC01, ZDC04, ZDC07, ZDC10 have [last_change_date] for the “cabinet” and other rows
ZDC06 has no [last_change_date] for the “cabinet” but does have for other rows

Capture-design-table-anomalies-5

Oh okay, Thank you.

Do you wish to have MAX date in case of only those records where [last_change_date] is for “Cabinets” and no other asset type?

Yes please… :slight_smile:

Oh ok, then please try , change highlighted in bold.

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”,
ISBLANK([last_change_date])
)))=0),

[id] =
MAXROW(
“rf_design”,
“last_change_date”,
AND(
[main_asset_name]=[_THISROW].[main_asset_name],
[lcs]=[_THISROW].[lcs], [source_asset_type] = “Cabinet”
)))

1 Like

Hi @Suvrutt_Gurjar,

Sorry… I was too quick with my reply…

I’d like the MAX date only, regardless of whether it’s a “Cabinet” or not…

In ZDC06’s case I’d only like to see the result with “Simon Says” in it and not the other (which is the “Cabinet”)… :thinking:

Hold the phone please… Let me keep testing… I removed the [source_asset_type] = “Cabinet” and it appears to be correct now… Testing some more and will get back to you ASAP… :slight_smile:

1 Like

@Suvrutt_Gurjar,

Dude… You are an absolute ROCK STAR… Works perfectly…!!!

There was no way that I was going to get to the same place without a little expert guidance.

Many thanks for your time once again…! :thinking: :+1: :slight_smile:

1 Like

Thank you for the update and you are welcome. Good to know it works the way you wish.

I believe it is a bit inefficient filter with three different multirow expressions( SELECT() and MAXROW()). Will post back if something strikes to make it more efficient.

2 Likes

Hi @Suvrutt_Gurjar,

I understand that and would really appreciate that.

My current priority - given my time and resource constraints - is to get a MVP/prototype into the field and then to work on efficiencies. The app falls short in the formatting rules area too and I will need to spend some time fixing that at a later stage!

Thanks again… :slight_smile:

1 Like