Measure with two different columns

[levialae]

Hi

I am trying to calculate how the energy use per ton of production. The energy use per day is in a different table than the production per day.

When I try to use the related function it get the infinite / NaN error. I am not sure how to solve this.

Energy

And production

[selimovd]

Hey @levialae ,

can you show the relationship?

Otherwise you should also be able to calculate that with the CALCULATE function and some modification of the filter context. What exactly do you need?

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[levialae]

Hi Denis

I have attached a screenshot of the relationship.

The relationship is between the two 'daily' columns. I have also tried the two 'DateTime' columns which I didn't change anything for me.

So what I need is:

The oil use daily divided by the daily tons produced (kiln 1 scale). This will give me how much oil did we use per ton of production. 

[ryan_mayu]

@levialae 

The relationship is many to many. You need to clean up the data and make it to be one to many or many to one.

[levialae]

Hi again Ryan

It might be a stupid question, but how would I go about cleaning up date data? 

[ryan_mayu]

@levialae 

I think you need to check

1. if there is any blank value for FQN

2. what's the reason caused many to many relationship? Is it correct that you can have values that does not exists in the other table?

at last i think you need a DIM table

Table 2 = DISTINCT(UNION(DISTINCT('Table'[FQN]),DISTINCT('Table (2)'[FQN])))

use this table and create one to many relationships with the current two tables.

[levialae]

Hi Ryan

There are no blank values for FQN. 

I believe the reason is that I have tried to make a relationship between the two tables, and the DateTime are often not the same, i.e.:

• 24 August 2021 10:04 for one table and
• 24 August 2021 10:01 for the other one

Can I ask (so I understand the process) why you want to create a relationship between the names (FQN)? Is that how you would normally do something like this?

[ryan_mayu]

@levialae 

sry ,my mistake. If the time is not important for you . try to move the time and only keep the date and create relationship again.

[levialae]

@ryan_mayu 

The time is actually quite important since the data will be visualized during the day for operators. However, the data will also be presented weekly - so I guess I can make a copy of the date table and remove the time and only keep dates, then use that date column instead of DateTime column?

[ryan_mayu]

@levialae 

you can also separte the datetime to date and time columns.

However, i think you still need a calendar table

[levialae]

Hi Ryan

I have taken some time to look into this calendar table. I have implemented one now - but I'm still not sure how to divide the two figures with each other. I don't have a direct relationship between the two tables, but they are both connected to the calendar table. 

[ryan_mayu]

@levialae 

i think calendar table can bring out the corresponding data from both tables. could you pls provide some sample data on this?

[levialae]

@ryan_mayu 

I'm not sure if this is what you mean by sample: 

I want to divide this value by the value in the table below. They are both connected to a calendar table with a many-to-one relationship.

[ryan_mayu · Accepted solution]

@levialae 

Yes, that's kind of sample data. However, you can simplify that and not the screenshot. It's very hard for us to create the solution based on the screenshot. You can just paste some data in the reply. At last, pls let us know what the result you want to get based on the data you provided.

i just created some simple data and provide a solution. pls see the attachment and check if this is what you want.

[levialae]

@ryan_mayu 

I got it now, thanks! The issue was the I was using the date from the oil consumption instead of my calendar table - not very clever of me!

[ryan_mayu]

you are welcome