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! Go to 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]
)))
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
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…
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…
User | Count |
---|---|
36 | |
31 | |
30 | |
20 | |
17 |