I have a Table from which I want to retrieve an item based upon two columns, both of which are picklists:
Those two picklists are referenced to other Tables.
I have two questions:
How should I write a FindRow statement for filtering on the values displayed in those columns? I've tried a variety of things but none seem to work
=FINDROW(TransportEmissions, "TransportEmissions[Geography]=%", "Australia")
=FINDROW(TransportEmissions, "TransportEmissions[Geography]=Australia")
=FINDROW(TransportEmissions, "TransportEmissions[Geography][Name]=Australia")
What about if I want to filter on a different column of the "picklisted" table? For instance, the Geography picklist contains an ISO3 reference i.e. AUS for Australia, and it's this which I'd actually like to filter on
I found the problem here: my Geographies table was full of hidden new lines ('\n'), which broke all of the match statements!
=FINDROW(TransportEmissions,"TransportEmissions[Geography]=% AND TransportEmissions[Transport Type]=%", "Australia", "Rail")
Returns the result I expect, addressing point 1 above.
I am still curious if I can rework it to search on the ISO3 field though?
=FINDROW(TransportEmissions,"TransportEmissions[Geography][ISO3]=% AND TransportEmissions[Transport Type]=%", "AUS", "Rail")
doesn't compile
Pankaj
June 22, 2021, 11:19pm
4
Hi @Arch-0a55
Hello and welcome to the community .
The following was my attempt to replicate the scenario.
Below is a table I created:
This screenshot shows the expression which I use to run a query similar to yours on the screen:
I think your query is fine. You may still be experiencing problems due to hidden characters in your data.
I hope this is helpful.
Thanks, this was helpful.
You were quite right - there were newline characters in the column headings, which broke the subreferencing.
1 Like
system
Closed
June 26, 2021, 8:47am
7
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.