Multiple concatenated columns - Best practice

[Ghuiles]

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.

[v-shex-msft]

Hi @Ghuiles ,

You can consider to add new column to store merged key values in query editor side, then remove original fields to reduce data structure complexity.

For original key fields, you can simply extract them based on separator.

Regards,
Xiaoxin Sheng

[Ghuiles]

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.

[v-shex-msft · Accepted solution]

Hi @Ghuiles ,

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

[Ghuiles]

Thanks @v-shex-msft !

I will try it in my next project.

Cheers. G