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…
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:
Jake_Naude:
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]> ))
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]
)))
Suvrutt_Gurjar:
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]
)))
Suvrutt_Gurjar:
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]
)))
1 Like
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
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?
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
Suvrutt_Gurjar:
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?
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…
1 Like
Jake_Naude:
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…!
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
Suvrutt_Gurjar:
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…
1 Like
