topic Re: Vlookup from a variable into a table in Management & Governance

Vlookup from a variable into a table

gabyala79 — Tue, 08 Feb 2022 15:56:19 GMT

Hi everyone!!

I would like to share you one case I'm struggling a lot to find some solution. Let me explain:

I have a Dashboard to follow performance of several org areas (and subareas) through the different months. All the KPIs work fine, and now I want to add one 'text box' with some comments related to the combination of:

Area X ; Date Y

So, I already managed to create an easy variable making the text for line above, and it is linked to the current selections in the app. In my example: "A ; 01/02/22".

Then, I loaded the table below in the app, and now the problem is that I do not know how to retrieve the comment with the entry of this variable.

Can someone guide me with some solution?

Thanks a lot!!

Re: Vlookup from a variable into a table

marcus_sommer — Wed, 09 Feb 2022 14:39:24 GMT

Why not a simple expression within the text-box with something like:

maxstring(Comments)

Depending on your real scenario you may also change the aggregation-function or add some set analysis or ...

- Marcus

Re: Vlookup from a variable into a table

gabyala79 — Wed, 09 Feb 2022 15:00:43 GMT

Thanks Marcus_sommer for your reply!!

Anyway, I do not understand how I can link the variable I already created to do 'Area X ; Date Y' to retrieve the correct row from the table.

Can you please help me on this?

Thanks

Re: Vlookup from a variable into a table

marcus_sommer — Wed, 09 Feb 2022 16:02:56 GMT

Ideally your comment-data are fully linked within the data-model and then you could use the mentioned approach. If they are not directly linked maybe because it's a loosen table you need nevertheless comparable data-structures. Means also a date- and an area-field. With it you could reference to your wanted data. It may look like:

only({< DateComment = p(DateFact ), AreaComment = p(AreaFact) >} Comment)

- Marcus

Re: Vlookup from a variable into a table

gabyala79 — Wed, 09 Feb 2022 16:40:19 GMT

Thanks again!

It is like you said, I have a table isolated from rest of the model, and I tried to get the comment doing:

maxstring(only({<IdComments = '3'>}[General Comments]))

just to test the syntaxis, but the following error appears:

"Nested aggregation not allowed

Am I doing something wrongly?

Thanks

Re: Vlookup from a variable into a table

marcus_sommer — Wed, 09 Feb 2022 17:29:31 GMT

Yes, it's not quite right. Aggregations couldn't be nested unless you wrap them with an aggr() to create an appropriate dimensionality for the (inner) aggregation. I don't think that you need it here. Just try it with:

maxstring({<IdComments = {'3'}>}[General Comments])

- Marcus