Forum Discussion

cbruhn42's avatar
cbruhn42
Helper III
1 year ago
Solved

Need Help Identifying Compaing Column Values

I have two tables in my model:

Table A

columns:  date time | Temperature

 

Table B

Run Start Date Time | Run End Date Time | Order ID | Reject Date Time | Reject Count

 

I'm trying to link the two tables so I can see if the temperature from Table A causes an increase in reject count in Table B.  I'm thinking I'd like to somehow combine the tables or link them so I can create a line chart with the date time and Reject Date Time being a single column on the x-axis, then the temperature and reject values on the y-axis.

Need Help
  • v-sshirivolu's avatar
    v-sshirivolu
    1 year ago

    Hi cbruhn42 ,
    I understand you're still facing the issue despite following the steps. Since I'm getting the expected output on my end, 

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

    Please show the expected outcome based on the sample data you provided.

    How to provide sample data in the Power BI Forum - Microsoft Fabric Community

    If you want to have your question answered quickly and avoid many back and forth, you should read this excellent article with lots of useful tips.

     

12 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi cbruhn42 ,

    To analyze the relationship between temperature (Table A) and rejects (Table B) over time in a single chart, you need to align their Date/Time fields, even though the tables are structured differently.

    Here’s a concise approach:

    1. Create a Master DateTime Table: Make a table with all Date/Time values covering your data range, at the desired granularity (minute/hour/day).

    2. Relate Both Tables to the Master Table: Create relationships from Table A's date time and Table B's Reject Date Time to the new master DateTime table.

    3. Build the Visual: Use the DateTime from the master table as your X-axis. Use temperature (from Table A) and reject count (from Table B) as Y-axis values. Power BI will line up both values on the timeline.


    Create a shared DateTime axis by relating both tables to a common DateTime table. This lets you compare temperature and reject counts together over time in your chart.

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi cbruhn42 

     

    It would be easier for us to provide a more suitable solution had you provided a workable sample data  (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

  • v-sshirivolu's avatar
    v-sshirivolu
    Community Support
    1 year ago

    Hi cbruhn42
    Thank you for reaching out to Microsoft fabric community.

    Try These steps -

    Create a DateTimeAxis Table

    DateTimeAxis =
    VAR MinDate = MIN(MIN(TemperatureData[date time]), MIN(RejectData[Reject Date Time]))
    VAR MaxDate = MAX(MAX(TemperatureData[date time]), MAX(RejectData[Reject Date Time]))
    RETURN
    ADDCOLUMNS (
    CALENDAR (MinDate, MaxDate),
    "Hour", HOUR([Date]),
    "Minute", MINUTE([Date])
    )

    Create relationships as follows:

    DateTimeAxis[DateTime] → TemperatureData[date time]

    DateTimeAxis[DateTime] → RejectData[Reject Date Time]

    Use single-direction, many-to-one relationships.

    Create Measures individually - 
    AvgTemperature = AVERAGE(TemperatureData[Temperature])
    TotalRejects = SUM(RejectData[Reject Count])

    Use Line Chart

    X-axis: Use DateTimeAxis with DateTime values.

    Y-axis: Include both AvgTemperature and TotalRejects.

    Please find the attached.pbix file for your reference.

    Regards,
    Sreeteja.




     

    • cbruhn42's avatar
      cbruhn42
      Helper III
      1 year ago

      This feels like it's close.  I created the date table, but for some reason it's not pulling in the time portion of the date/time columns.

       

      Also, I don't want to look at the average temperature. I just want to chart the temperature and reject count against each other over time.

      • v-sshirivolu's avatar
        v-sshirivolu
        Community Support
        1 year ago

        Hi cbruhn42 ,

        Date Table  - Time portion
        This issue typically occurs when Power BI automatically aggregates or groups data by date only.

        To resolve this, please verify that the DateTime column in your DateTimeAxis table is set to the Date/Time format, not just Date. In Data view, select DateTimeAxis[DateTime] and set the Data Type to Date/Time.

        Within your chart's X-axis settings, set the Type to Categorical rather than Continuous. This approach ensures that exact timestamps are displayed, preventing automatic grouping into days or hours.

        Chart actual temperature

        If you wish to chart each raw temperature reading over time, rather than an aggregated value, you do not need a measure. Simply drag TemperatureData[Temperature] to the Y-axis and DateTimeAxis[DateTime] to the X-axis. Similarly, you can add RejectData[Reject Count] to the Y-axis.

        However, since Power BI only allows measures on a dual-axis, you may alternatively create two simple measures:

        TemperatureRaw = MAX(TemperatureData[Temperature])
        RejectCountRaw = MAX(RejectData[Reject Count])

        These measures will function as raw readings if your X-axis is sufficiently detailed, such as by minute.

        Thank you.