cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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
Highlighted
Microsoft
Microsoft

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.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors