Forum Discussion

levialae's avatar
levialae
Frequent Visitor
5 years ago
Solved

Measure with two different columns

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 th...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    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.

ryan_mayu's avatar
ryan_mayu
Super User
5 years ago

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's avatar
levialae
Frequent Visitor
5 years ago

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's avatar
    ryan_mayu
    Super User
    5 years ago

    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's avatar
      levialae
      Frequent Visitor
      5 years ago

      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's avatar
        ryan_mayu
        Super User
        5 years ago

        levialae 

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

        However, i think you still need a calendar table