DAX combine/merge/link similar names in a column

[Anonymous]

Hi, 

This is my Table, e.g.: 

SDR ID  -   Site  -   CP ID   -    Start Date

1456     -  Glasgow - APT3456 - 03 April 2021

17893    - London   - 7479  - 05 May 2022

1802      - Glasgow  - 3456 -  07 July 2022

18634    - Bristol    - 8934   - 01 Feb 2021

28796    - Glasgow  - APT3456  - 12 April 2021

I am using this DAX to calculate Average number of Sessions per Charger per day: 

SDR ID average per CP ID average per Day =

AVERAGEX(

VALUES('FACT TABLE'[Start Date]),

CALCULATE([SDR ID average per CP ID])

)

The issue is that the CP ID e.g. 3456 and APT3456 are the same item. The name has changed over time and there are quite a few like these in my data. 
How do I merge or link the simmilar numbers together in order to use my DAX to calculate the averages?

Best, 

J

[SpartaBI · Accepted solution]

@Anonymous best option is to clean the data before you bring it to the model and replace the old names with their new names and then you won't need to write complex DAX 🙂 but that is alwasys an options but in this case I will need the complete logic of what needs to be what (a sample of 1 is not enough as it needs to work on all) and then I could write the code. I really suggest you just do modeling cleaning option (you can also do that in DAX by adding a surrogate column that will contain only new names and use that column in your measure instead of the original column.

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

Thank you SpartaBI, Yes, as soon as I sent my message I realised that those numbers are one thing so cleaning the data first is probably the only way to go. 
Much appreciated.

Best, 

J

[SpartaBI]

@Anonymous my pleasure 🙂
Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. Please give it a thumbs up over there if you liked it 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543