Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Snuchiduchi
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
v-kkf-msft
Community Support
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

v-kkf-msft
Community Support
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]))
    )
)

v-kkf-msft_0-1616469877220.png

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
Super User
Super User

@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] )
)

jdbuchanan71_0-1616193592036.png

 

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. 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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