Need Help - Cross-Table Query Into Matrix Visual

[Fuzz_ZA]

Greetings Community.

Firstly, I'm new to Power BI with 1 week. I am building a dashboard for our department using DirectQuery. The dashboard connects to two tables in a SQL DB. The primary data table "MIData" contains various columns which most of the dashboard will be composed from. The second table "MIMetrics" is static with the purpose of storing threshold values and is small and will never change. Both tables contain a column called "processName" and my intent was to use that column to create a Power BI realtionship.
"MIData" has the following structure:

	processName	itemCreated	itemEnd	attemptWorkTime	itemStatus	itemDescription
1	Process 1	2025-08-01 13:14:15	2025-08-01 14 22:16	168	Referred	Case referred to dept.
2	Process 1	2025-08-01 13:14:23	2025-08-01 14 23:18	145	Completed	Case processed
3	Process 2	2025-08-01 15 08:35	2025-08-01 15 38:51	182	Referred	Case referred to dept.
4	Process 3	2025-08-02 09:22:16	2025-08-02 10:44:23	143	OnHold	Pending information

"MIMetrics" has the following structure:

processName	Completed	Referred	OnHold
Process 1	00:05:00	00:03:00	00:07:00
Process 2	00:02:30	00:01:00	00:04:15
Process 3	00:03:00	00:08:00	00:04:45

MIMetrics is used to measure whether "Process 1" and its assoc. column names (which are time(0) SQL data types) were above or below the stated time in the column.

I have built a basic matrix view with a single-select slicer based on "processName" from "MIData" and works fine and shows the distinct "processName". I want to add a column "Threshold" (green text) to the matrix to show the threshold values from "MIMetrics" by Status and matching "processName".

My current dashboard page resembles this:

Slicer Selection
Process1

Status	Count	Pct of Total	Average Transaction Time	Threshold
Referred	6	22.20%	00:04:22	00:03:00
Completed	12	44.40%	00:02:47	00:05:00
OnHold	9	33.30%	00:05:28	00:07:00

I cant seem to find a way to create that "Threshold" column in the matrix visual that only shows the value from the related "MIMetric" table. Any help would be highy appreciated.

[v-echaithra · Accepted solution]

Hi @Fuzz_ZA ,

Thank you for reaching out to Microsoft Community.

Create a DAX Measure for Threshold, we need a measure that will return the correct threshold value from MIMetrics, depending on:

The selected processName via slicer,

The itemStatus shown in the matrix row.

Threshold Time =
VAR selectedStatus = SELECTEDVALUE(MIData[itemStatus])
VAR thresholdTime =
SWITCH(selectedStatus, "Completed", MAX(MIMetrics[Completed]),
"Referred", MAX(MIMetrics[Referred]),"OnHold", MAX(MIMetrics[OnHold]),
BLANK())
RETURN
thresholdTime

Hope this helps.
Warm Regards,
Chaithra E

[Fuzz_ZA]

This is exactly what I did in the end using SWITCH() but took me a while to figure it out. It's like you read my mind 🙂 Marking yours as "Accepted Solution".

[FBergamaschi]

What is this Threshold? Do we need to calculate  it or is it available in a column (I cannot find it in your data)?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI