cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jan_Orlandini Frequent Visitor
Frequent Visitor

Reshape filter context - or: How to dynamically point to a matrix value in DAX

Dear all,

 

I'm currently stuck with a problem, that puzzles me since a couple of weeks. I hope I can explain it in the correct terms...

 

Goal: I'd like to calculate the potential capacity of a production line for given down-time incidents.

 

Data structure:

A) "Incident table" with              EventID, Start/End Timestamp, Classification, LineID, ...

B) "Line load table" with             Timestamp, Width, Speed, LineID...

 

From A) I can easily calculate the total downtime either per incident or aggregated to category of incidents or per line.

From B) I can easily calculate average width and speed per production line and year

 

Issue: I'd like to calculate the potential capacity gain (if no incident occured). The math is simple: Width * Duration * Speed. BUT: It should not apply the "current" speed matching the timestamp (because, during an incident the speed is usually 0). Instead it is an agreement with Management Accounting to use the average speed and average width of that very production line in the given year. Now the "final" but: For clarity reasons I'd like to have a table that gives the potential capacity only by incident category -> w/o line & year on the rows - so I am not able to use year & lineID as filter context in the table!

 

Question: If I put my measure for average speed in a matrix (a.k.a. Pivot table) by year and line I have all the right values there. But how to pick one of these values to employ it in a measure (and secondly: how to dynamically reference to the right one?)

 

I very much hope, that there's someone out there, who can understand my issue!

Thanks in advance to all datanauts...

1 REPLY 1
Community Support Team
Community Support Team

Re: Reshape filter context - or: How to dynamically point to a matrix value in DAX

Hi @Jan_Orlandini ,

Could you please offer a sample data and post your desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.