topic Re: DAX Column lookup in existent columns in DAX Commands and Tips

DAX Column lookup in existent columns

cosminc — Wed, 10 Jul 2024 09:48:11 GMT

Hello,

I need in DAX for a table like this to obtain Column Needed, where table is more complex with another column and i want to do this dinamically not to define in column measure Column A, B etc

Value Column A Column B Column C Column Needed

A 10 0 5 10

B 9 7 3 7

A 8 5 4 8

C 0 2 5 5

Thanks,

Cosmin

Re: DAX Column lookup in existent columns

bhanu_gautam — Wed, 10 Jul 2024 09:53:41 GMT

cosminc , It looks like you are trying to get maximum value of each table you can do that using measure which uses selectedcolumns and union

Column Needed =
MAXX(
UNION(
SELECTCOLUMNS(
TableName,
"Value", TableName[Column A]
),
SELECTCOLUMNS(
TableName,
"Value", TableName[Column B]
),
SELECTCOLUMNS(
TableName,
"Value", TableName[Column C]
)
),
[Value]
)

Re: DAX Column lookup in existent columns

rajendraongole1 — Wed, 10 Jul 2024 10:09:41 GMT

Hi cosminc - you can try below calculated column to get the max value for each column

Column Needed_1 =

MAX(

'LKP'[Column A],

MAX(

'LKP'[Column B],

'LKP'[Column C]

)

Hope it works. I am getting 9 instead of 7 as per your output.

check it and let know

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Re: DAX Column lookup in existent columns

cosminc — Wed, 10 Jul 2024 10:13:38 GMT

Hi,

but how can we do it dinamically, i don't know column names they can change

Thanks,
Cosmin

Re: DAX Column lookup in existent columns

rajendraongole1 — Wed, 10 Jul 2024 10:19:55 GMT

Hi cosminc - in such cases dax formulae will not works , you can try to implment in calculate the maximum value from columns in Power BI

go to add column >> custom column

List.Max(Record.ToList(Record.RemoveFields(_, {"Value"})))

I hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Re: DAX Column lookup in existent columns

cosminc — Wed, 10 Jul 2024 11:16:21 GMT

no maximum

if value A from A column and so on but dinamically, column names and values can change

thanks

Re: DAX Column lookup in existent columns

Anonymous — Mon, 15 Jul 2024 08:43:27 GMT

Hi cosminc

rajendraongole1 bhanu_gautam Thank you very much for your prompt reply.

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

Re: DAX Column lookup in existent columns

cosminc — Mon, 15 Jul 2024 15:26:54 GMT

Hello,

unfortunately not yet

Thanks

Re: DAX Column lookup in existent columns

Anonymous — Tue, 16 Jul 2024 06:47:19 GMT

Hi cosminc

Here I offer a method:

Go to the power query and select Custom Column in the Add Column tab.

List.Max({[ColumnA], [ColumnB], [ColumnC]})

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX Column lookup in existent columns

cosminc — Tue, 16 Jul 2024 07:11:26 GMT

Hi,

thanks but i don't need max, what i need is if Value is A to return item from A column, if value is B to return the item from same row from B column
where items from column Values are corespondent with available column names; all this should be dinamically so that when it appears a Value X and a column X will not be necessary to modify the pbix.

Thanks,
Cosmin

Re: DAX Column lookup in existent columns

Anonymous — Tue, 16 Jul 2024 08:38:06 GMT

Hi cosminc

Perhaps you could try the following:

Create a column.

Column Needed = var _value = 'Table'[Value] RETURN SWITCH( TRUE(), _value = "A", 'Table'[ColumnA], _value = "B", 'Table'[ColumnB], _value = "C", 'Table'[ColumnC] )

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX Column lookup in existent columns

cosminc — Tue, 16 Jul 2024 08:47:36 GMT

close but is not dynamic, the scenario: new items will appear, same with columns.

i need to work in this scenaro without to be necessary to modify dax

thanks,
Cosmin