Forum Discussion
Using two fields to create a combination key
Hi,
I am in need of advice on whethe rit is possible to create a combination of two fields to give me unique values.
For example i have a transactional database:
ID DATE G_AMT T_AMT N_AMT PRODUCT Product_N_Amt
1 1/1/16 10000 2000 8000 A 3000
2 1/2/16 12000 3000 9000 B 4500
3 1/3/16 16000 4000 12000 A 6000
1 1/1/16 10000 2000 8000 B 2000
1 1/1/16 10000 2000 8000 C 3000
2 1/2/16 12000 3000 9000 D 4500
3 1/3/16 16000 4000 12000 E 6000
So here if i was to use the n_amt field it aggregates all of the invoice amounts for example invoice id 1 would have a total of 24000 where it should simply be only 8000. So i need to be able to use the combination of the invoice id and date to ensure it only aggregates to that level.
What would be the best way to do?
Many Thanks in advance for your advice.
Thanks,
Bal
BalAW looking at your dataset even combination of ID and DATE doesn't make it unique for example ID = 1 and DATE = 1/1/16 is twice. What you need is something called Composite Primary Key which is a primary key created using more than one column. You need to create that at data source level and import it into power bi and use that against N_AMT field. That would be the best approach.
Other way you can do is to declare N_AMT field as Data Type of Text in Power BI but that means you will only be able to use it in few visuals such as Table or Matrix since in other values you need Data Type of Number to drop into Values section.
My suggestion is to create primary key of combination at data source level and import into power bi or you can also use DAX in power bi to create something unique to say combination of ID + DATE + Incremental number from 1 till end of rows so that each rows has unique value.
1 Reply
BalAW looking at your dataset even combination of ID and DATE doesn't make it unique for example ID = 1 and DATE = 1/1/16 is twice. What you need is something called Composite Primary Key which is a primary key created using more than one column. You need to create that at data source level and import it into power bi and use that against N_AMT field. That would be the best approach.
Other way you can do is to declare N_AMT field as Data Type of Text in Power BI but that means you will only be able to use it in few visuals such as Table or Matrix since in other values you need Data Type of Number to drop into Values section.
My suggestion is to create primary key of combination at data source level and import into power bi or you can also use DAX in power bi to create something unique to say combination of ID + DATE + Incremental number from 1 till end of rows so that each rows has unique value.