Hi
this is my first post. I think this question has been asked already before but I wasn't able to understand the answers so please be patient with me.
I'm quite new in PowerPivot and I am trying to convert an old excel file in a new one using PowerPivot.
In my PowerPivot I have two tables (linked to excel tables):
- the first table called "dimCars"<dimcars> with two fields: [BrandID] and [Brand]
- the second table called "factSales"<factsales> with three fields: [Date], [BrandID], [CustomerID]
Extract of the two tables as follows:
<tbody>
</tbody>
<tbody>
</tbody>
The relationship between the two tables is a one to one relationship between the [BrandID] Primary Key in the parent table "dimCars" and the [BrandID] Foreign Key in the child table "factSales"<dimcars[brandid]><factsales[brandid]> .
In the second table ("factSales"<factsales>) in PowerPivot I want to add a calculated column that will lookup the [BrandID] value and will return the corresponding [Brand] value from the <dimcars> table "dimCars".
In a few words, I want to do a VLOOKUP formula (I wrote the Excel VLOOKUP formula in the table "factSales" for more detail), but i just want to do it in PowerPivot using DAX.
Until now I searched everywhere in google and in several posts but didn't find the answer to this.
Am I wanting to do something wrong?
Is there a way to do this in PowerPivot?
Your reply(es) would be greatly appreciated!
Thanks in advance
Frank</dimcars></factsales></factsales[brandid]></dimcars[brandid]></factsales></dimcars>
this is my first post. I think this question has been asked already before but I wasn't able to understand the answers so please be patient with me.
I'm quite new in PowerPivot and I am trying to convert an old excel file in a new one using PowerPivot.
In my PowerPivot I have two tables (linked to excel tables):
- the first table called "dimCars"<dimcars> with two fields: [BrandID] and [Brand]
- the second table called "factSales"<factsales> with three fields: [Date], [BrandID], [CustomerID]
Extract of the two tables as follows:
table: "dimCars"<dimcars></dimcars> | |
BrandID | Brand |
1000 | VW |
1001 | Audi |
1002 | Mercedes Benz |
1003 | Toyota |
1004 | Hyundai |
1005 | Volvo |
1006 | Renault |
1007 | Nissan |
<tbody>
</tbody>
table: "factSales"<factsales></factsales> | |||
Date | BrandID | CustomerID | CalculatedColumn: Brand |
11.12.2013 | 1000 | A001245 | =VLOOKUP([@BrandID];dimCars[#All];2;FALSE) |
24.01.2014 | 1004 | A002486 | Hyundai |
16.02.2014 | 1001 | B00486 | Audi |
27.02.2014 | 1002 | C04663 | Mercedes Benz |
01.03.2014 | 1003 | A003496 | Toyota |
18.03.2014 | 1006 | Z45069 | Renault |
29.03.2014 | 1007 | YA48060 | Nissan |
02.04.2014 | 1004 | D048804 | Hyundai |
<tbody>
</tbody>
The relationship between the two tables is a one to one relationship between the [BrandID] Primary Key in the parent table "dimCars" and the [BrandID] Foreign Key in the child table "factSales"<dimcars[brandid]><factsales[brandid]> .
In the second table ("factSales"<factsales>) in PowerPivot I want to add a calculated column that will lookup the [BrandID] value and will return the corresponding [Brand] value from the <dimcars> table "dimCars".
In a few words, I want to do a VLOOKUP formula (I wrote the Excel VLOOKUP formula in the table "factSales" for more detail), but i just want to do it in PowerPivot using DAX.
Until now I searched everywhere in google and in several posts but didn't find the answer to this.
Am I wanting to do something wrong?
Is there a way to do this in PowerPivot?
Your reply(es) would be greatly appreciated!
Thanks in advance
Frank</dimcars></factsales></factsales[brandid]></dimcars[brandid]></factsales></dimcars>