Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 Sa...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks Anonymous !

    I will try it in my next project.

    Cheers. G