Need Help Identifying Compaing Column Values

[cbruhn42]

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.

[v-sshirivolu]

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]

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]

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.

[cbruhn42]

So I checked and the DateAxis table has the column Date marked as a Date/Time and I've tried setting the x-axis to both categorical and continuous.  Still having the same issue where nothing charts when I use Date on the x-axis and temperature on the y-axis.

[v-sshirivolu]

Hi @cbruhn42 ,

To create the best visualization, use a Line Chart with DateTime on the X-axis:

Choose a Line Chart.

X-axis:  date time (not DateAxis) directly onto the axis.

Y-axis:  Temperature onto the axis.

Formatting:

Set the X-axis to Continuous.

Ensure the X-axis data type is set to Date/Time.

[cbruhn42]

These are teh steps I have taken and nothing is showing up.  Just a blank chart. I'm stumped.

[v-sshirivolu]

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.

[v-sshirivolu]

Hi @cbruhn42,

Kindly share sample data that fully represents your issue or question in a usable format (avoid screenshots).

Ensure that no sensitive or unrelated information is included.

Also, please provide the expected result based on the sample data you've shared.

Thank you!

[danextian]

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.

[rohit1991]

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.