cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## How do you exclude name columns due to inconsistent naming conventions

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!

4 REPLIES 4
Community Support

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

Community Support

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.

Super User

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
Not applicable

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors