Forum Discussion
Multiple concatenated columns - Best practice
Dear Community,
I have a table about the Sales Order Changes. In order to have the proper Distinct Counts i would need the key columns a below.
| Country | Sales Order | Sales Order Detail | Sales Order Sequence | Value | Key Sales Order | Key Sales Order Seq |
| A | 11 | 34 | 12 | 10 | A-11-34 | A-11-34-12 |
| B | az | 12 | 22 | 15 | B-az-12 | B-az-12-22 |
This table when in production will have millions of rows.
I am afraid that these "key" columns will consume quite some space.
Is there a best practice here? Could one column somehow be enough (Key Sales Order Seq), but then how to make the distinct count at Order Detail level??
Additionaly, does it play a role if i add these columns via DAX or M?
Cheers. G.
Hi Anonymous ,
Yes, I think it should suitable for your requirement. For extract original fields values, you can refer to following dax formula:
Test = PATHITEM(SUBSTITUTE([Column],"-","|"),1)
Notice: Bold part is position of merged string, you can modify to different number to extract correspond fields value.
Regards,
Xiaoxin Sheng
4 Replies
Hi!
So, if i correctly understood, you are proposing:
- to create a column with the most detailed key i need. In the example then A-11-34-12
- if possible remove the columns that were base for that key
- if i need only a part of the key, e.g. the first 3 postions, then extract it in DAX
For example with a formula like calculate(distinctcount(mid(...
I am right?
Best regards. G.
Hi Anonymous ,
Yes, I think it should suitable for your requirement. For extract original fields values, you can refer to following dax formula:
Test = PATHITEM(SUBSTITUTE([Column],"-","|"),1)
Notice: Bold part is position of merged string, you can modify to different number to extract correspond fields value.
Regards,
Xiaoxin Sheng