How do you exclude name columns due to inconsistent naming conventions

[Snuchiduchi]

The database has consistent job numbers but the job names are not consistent.  

Each month as data was loaded, the job numbers remained the same (correct) but whoever loaded them put slighltly different names each month (incorrect). 

The result is that when BI compares one period to the next, it doesn't think they are the same job because the name column is always a little different. 

Example:

MONTH    JOB      NAME                                                   Contract       Cost to Date   Cost this Month 

JAN 2020  10000   St.Mary's Hospital Out Patient            1,000,000       100,000             100,000               

FEB 2020   10000   St.Mary's MOB Out Patient                 1,000,000       250,000             250,000

In this example BI thinks that the month to date costs for Feb were 250,000 because it doesn't recognize the January info as related.  The actual month to date cost in Feb was 150,000, not 250,000. 

Example measure:

MTD Cost =

Calculate(SUM('Fact Table'[Cost to Date])) - CALCULATE(SUM('Fact Table'[Cost to Date]),DATEADD(DateTable[DATE],-1,MONTH))

THANK YOU!

[v-kkf-msft]

Hi @Snuchiduchi ,

Did I answer your question? If you can get the correct result, please accept as solution so that users with the same problem can find it quickly. If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft]

Hi @Snuchiduchi ,

Modify the formula as:

MTD Cost = 
SUM('Fact Table'[Cost to Date]) - CALCULATE(
    SUM('Fact Table'[Cost to Date]),
    FILTER(
        ALL('Fact Table'),
        'Fact Table'[JOB] = MAX('Fact Table'[JOB])
        && MONTH('Fact Table'[Date]) = MONTH(MAX('Fact Table'[Date]))-1
        && YEAR('Fact Table'[Date]) = YEAR(MAX('Fact Table'[Date]))
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[jdbuchanan71]

@Snuchiduchi 

Another option is to add a calculated column to your table, this one assumes there is a date of some kind to find the first instance of the name and use it.

Names Cleaned = 
CALCULATE (
    FIRSTNONBLANK ( 'Table'[name], MIN ( 'Table'[Date] ) ),
    ALLEXCEPT ( 'Table', 'Table'[Job] )
)

[Anonymous]

There are likely a number of ways you can solve this, but the way I would do it is, 

Duplicate the dataset, on the new dataset [Table2] Keep only "Job" and "Name", & remove duplicates on Job number column. Now you're left with a table of unique value job numbers, each with a corresponding name. 

Now just create a one to many relationships using the "Job" columns, and use the "Name" from [Table2] in your matrix.