New column to categorize building merges or splits

[omillzy]

I am working with commercial property data and need to categorize if a building has been merged or split. We don't have a merge or split field in the database, just a mapping table between the actual office suites and the proposed office suites.

To keep things simple, the 'actual' suites are the old office spaces and the 'proposed' suites are the new space after the split or merge has taken place.

An example of a merge is: 

13A, 111 Piccadilly
13B, 111 Piccadilly
13c, 111 Piccadilly

merging to

13th floor, 111 Piccadilly
13th floor, 111 Piccadilly
13th floor, 111 Piccadilly

And an example of a split would be:

2.22 & 2.23, Platform
2.22 & 2.23, Platform

split to

2.22, Platform
2.23, Platform

I've created a (crude) calculated column based off the length of the actual name against the proposed, but this doesn't work for all cases as you can imagine.

Merge or Split = 
IF(LEN('Proposed to Actual Suite Mapping'[Actual_Name]) <= LEN('Proposed to Actual Suite Mapping'[Proposed_Name]), "Merge", "Split")

Does anyone have any ideas on how I might achieve this using DAX or M?

https://drive.google.com/open?id=1eK793KQD4r7EMT3clBpmTwnOnAH0c-q0

Cheers

[Icey]

Hi @omillzy ,

Maybe you can try this:

Merge or Split = 
IF (
    'Proposed to Actual Suite Mapping'[Actual' Name] = 'Proposed to Actual Suite Mapping'[Proposed' Name],
    BLANK (),
    IF (
        LEN ( 'Proposed to Actual Suite Mapping'[Actual' Name] )
            < LEN ( 'Proposed to Actual Suite Mapping'[Proposed' Name] ),
        "Merge",
        IF (
            LEN ( 'Proposed to Actual Suite Mapping'[Actual' Name] )
                = LEN ( 'Proposed to Actual Suite Mapping'[Proposed' Name] ),
            BLANK (),
            IF (
                LEN ( 'Proposed to Actual Suite Mapping'[Actual' Name] )
                    - LEN ( 'Proposed to Actual Suite Mapping'[Proposed' Name] ) = 1,
                "Merge",
                "Split"
            )
        )
    )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[omillzy]

@Icey 

Thanks for the code, I've compared your classifications to mine, and there isn't much difference. However, it's still relying entirely on the length which won't work if the building names are the same length (but have changed) - and there are probably other cases as well.

I was thinking of some other logic, using parent/child groups or looking at patterns in the text (there seems to be an '&' or 'and' in the merged cases)

[Icey · Accepted solution]

Hi @omillzy ,

If you want to use "&" and "and", you can try to use Search.

Best Regards,

Icey