Calculate on variable column

[Anonymous]

Hi all,

In excel i'm quite familair with the index/match function of xlookup. When trying to achieve this in Power BI i'm not getting the results i'm looking for. In Excel it seems easier to lookup information in a specific column based on variables than in Power BI.

After some investigation it seems easier to unpivot the table in Power Query and use LOOKUPVALUE. 
Nevertheless i wanted to check if it is possible to maintain the below setup/data and apply a formula to it.

For example:
KG = 2 and CountryIsoCode = DE should result in 4
KG = 4 and CountryIsoCode = FR should result in 10

KG	GB	DE	BE	FR	NL	ES
1	1	2	3	4	4	5
2	2	4	6	7	8	9
3	3	6	7	8	9	10
4	5	10	9	10	11	12

[Anonymous]

The be clear about my request. I'm looking for the possibility to lookup information in a specific column based on a value in another table. 
So if the the value is "DE" the formula needs to lookup information in the "DE" column. 

Is this possible in Power BI ?

[ChiragGarg2512]

@Anonymous , Value can not be used to access another column. It can not work as column/measure = 'Table'[value] where value is taken from another filter or slicer, here using value would mean that there is a column/measure by the name of value.

[Anonymous]

Hi @ChiragGarg2512 ,

Ok that is clear. Thank you for your reply and explanation.

I will continue to unpivot the data and use filter or lookup to retrieve the required information. 

[ChiragGarg2512]

@Anonymous , As KG is the costant field and the remaining fields are the attributes. Select all the other fields or select the KG field and use the unpivot option. The unpivot can be used in two ways:

1) Select KG and use unpivot the other columns

2) Select all the other and unpivot them.

The table will look like:

Now have two slicers, one for KG and other for the attributes and a card visual for the value.

This should solve the problem.

Thank You

[Anonymous]

@Hi @ChiragGarg2512 ,

Thanks for you help.
I most likely wasn't not clear in my request as i know how to use the unpivotted table but i'm looking for the formula to lookup the correct value/rate based on weight and country in the original table. 

[devesh_gupta]

@Anonymous Hi there,

To achieve the desired result of looking up information in a specific column based on variables in Power BI, you can use the LOOKUPVALUE function. The LOOKUPVALUE function returns the value for the row that meets all criteria specified by one or more search conditions.

In your case, you can use the LOOKUPVALUE function to look up the value in the specified column based on the values of “KG” and “CountryIsoCode”. Here’s an example of how you can use the LOOKUPVALUE function:

Result = LOOKUPVALUE(
    'YourTableName'[ColumnName],
    'YourTableName'[KG], 2,
    'YourTableName'[CountryIsoCode], "DE"
)

Replace 'YourTableName' with the actual name of your table, and 'ColumnName' with the name of the column you want to retrieve the value from.

By using the LOOKUPVALUE function, you can look up information in a specific column based on variables in Power BI.

If you find this insightful, please provide a Kudo and accept this as a solution.

[Anonymous]

Hi @devesh_gupta ,

Thanks for your help. 
The provided formula i understand and also have written myself. The challenge is to get the country as variable. In this case you used "DE" but depending on the  country in the row it needs to calculate it for it can be also an other country. 
How to make this country variable?

EDIT: 
Lets call the table in the first post table1.
Below is table2, table2 needs to lookup information from table1

Order#	Country	Weight	Rate
1	DE	2	?
2	FR	3	?
3	ES	5	?