topic Function to get a value to be used in a filter in DAX Commands and Tips

Function to get a value to be used in a filter

leandro_vegacr — Tue, 24 Jan 2023 21:30:17 GMT

Hi everyone.

I have barely used Power BI before, I mostly work with Automate and Power Apps. Now, using the new Power BI connector in Flow, I need to get a table composed of columns from many tables in the dataset as a JSON. I've been analyzing the relations between tables in the dataset and I think I have worked on how to do it; I hope to use the SelectColumns function and with the Related functions, get the columns values I need from tables different to the one I'm basing the query on.

However, I'm a little stuck with how to get a filter to work. I suppose this may be pretty easy, but any help is apreciated. The user is going to enter a code in a search bar. That code is going to be used to get a single row in a "Codes" table, which happen to have also a "key" columns. That "key" column is going to be used to get a single row in the "Data" table, the table I'll use as the base to get my JSON built in.

So my question is: how can I get the "key" column as a scalar value to filter the "Data" column based on it, if I feed it the "code" column? I may be thinking too off Power BI, maybe, so if there is an easy way to do it, I'm fully open to try 🙂

Hope the question is clear.

Thank you very much!

pd: I've researched about the Filter functions, but there does not seem to be something like table.value, to get a value from a table...

pd2: Would this be a good way to do it?:

Filter(

Data,

Data[Key] = LOOKUPVALUE(Codes[Key],Codes[Code],STRING_ENTERED_BY_USER)

)

Re: Function to get a value to be used in a filter

tamerj1 — Wed, 25 Jan 2023 10:15:50 GMT

Hello leandro_vegacr
Please provide a snapshot of the relevant part of your data model along with the relationships and sample expected result.

Re: Function to get a value to be used in a filter

leandro_vegacr — Wed, 25 Jan 2023 13:28:46 GMT

Hi tamerj1 , pleased to!

PedidosJDEQuery is the point of access to the app, there is a pedidosJDE field that is the input the requester is going to provide.

From there, there is a field called chaveApp that relates with pedidosJDEDirect table, and that I need to use to filter pedidosJDEDirect table. which is going to be the base table. Again, I need to build a new table with fields from all the tables related with PedidosJDE_Direct.

Right now I have a working query, taking advantage of all the related tables. It is this:

EVALUATE(
SELECTCOLUMNS(
Filter(
PedidosJDE_Direct,
PedidosJDE_Direct[CHAVEAPP] = LOOKUPVALUE(PedidosAppComPedidoJDE[ChaveApp],PedidosAppComPedidoJDE[PedidoJDE],USER_INPUT)
),
"Regional", RELATED(PedidosAppComPedidoJDE[Regional]),
"Subregional", Related(Clientes[subarea_name]),
"CD_Clientes", PedidosJDE_Direct[TP PEDIDO],
"CPF_Cliente", RELATED(Clientes[farmer_cnpj_cpf]),
"Inscricao_estadual",RELATED(Fazendas[IE]),
"Address_fazenda",RELATED(Fazendas[ADDRESS]),
"Fazenda",RELATED(Fazendas[NOME]),
"Municipio_fazenda",RELATED(Fazendas[CIDADE]),
"UF_fazenda",RELATED(Fazendas[UF]),
"Frete", PedidosJDE_Direct[TP FRETE],
"Data_inicio_entrega",PedidosJDE_Direct[INICIO ENTREGA],
"Data_fin_entrega",PedidosJDE_Direct[FINAL ENTREGA],
"Vendedor",RELATED(Clientes[responsible_user_name]),
"Grupo_item",PedidosJDE_Direct[GRUPO]
)
)

Thanks for taking a look.

Re: Function to get a value to be used in a filter

tamerj1 — Wed, 25 Jan 2023 14:19:41 GMT

leandro_vegacr
Nothing is wrong with your code except that I don't trust LOOKUPVALUE as it might generate errors depending on the selection and the possibility of blank values inside the table.
'PedidosAppComPedidoJDE' table filters 'PedidosJDE_Direct' table therefore, you can use CALCULATETABLE instead of FILTER as follows

= CALCULATETABLE ( PedidosJDE_Direct, PedidosAppComPedidoJDE[PedidoJDE] = USER_INPUT )

Other than that, the SELECTCOLUMNS function is perfectly used in its place.

Re: Function to get a value to be used in a filter

leandro_vegacr — Wed, 25 Jan 2023 19:26:44 GMT

Cool, thank you very much!