Trouble with expression to filter a slice

Jake_Naude
Participant V

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…

3X_b_a_ba37dae11a9129f516298a1627a7abfb97fed696.png

Solved Solved
0 15 432
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]
)))

View solution in original post

15 REPLIES 15

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…

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…

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 ?

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

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

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

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

3X_3_2_32c68a5755b1624105259378b77d8ba92903e8de.png

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…

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

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”)…

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…

@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…!

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.

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…

Top Labels in this Space