DAX Column lookup in existent columns

[cosminc]

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

[v-nuoc-msft]

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

[cosminc]

Hello,

unfortunately not yet

Thanks

[v-nuoc-msft]

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.

[cosminc]

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

[v-nuoc-msft]

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.

[cosminc]

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

[cosminc]

Hi,

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

Thanks,
Cosmin

[rajendraongole1]

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!!

[rajendraongole1]

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!!

[bhanu_gautam]

@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]
)

[cosminc]

no maximum

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

thanks