Using two fields to create a combination key

[BalAW]

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

[ankitpatira · Accepted solution]

@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.