Dynamically show dimension values from the latest transaction in a group

[JānisBojars]

I have a transaction table of Container movements (to customer and back to warehouse).

How to make data architecture to get requirements?:
To show in a table visualization latest records (dimension values) of each Container_Id by MovementDateTime?

I made a Metric with a sign 1, if it is a latest record and a solution worked well, when a Container_Id was filtered, but got an error of calculation limit, when I wanted to show all Container_Id.

Any suggestions? How to filter dynamically (using Date filter) latest movement records of each Container_Id?

[v-veshwara-msft]

Hi @JānisBojars ,

Just wanted to follow up on this thread,

From your description, the current measure-based approach works when a single Container_Id is in context but is likely hitting evaluation limits when applied across a larger dataset in a table visual.

In such cases, instead of identifying the latest record using a row-level measure, a more efficient approach is to first derive the latest MovementDateTime per Container_Id using aggregation and then filter the table based on that result. This can be implemented either in DAX using aggregation functions or upstream in Power Query.

For reference, Microsoft documentation on grouping and aggregation in Power Query can be found here:
How to GROUP BY or summarize rows - Power Query | Microsoft Learn

Similar threads:
Solved: Re: How to write a DAX to get category for most re... - Microsoft Fabric Community

Solved: Summarise table to show last record using a calcul... - Microsoft Fabric Community

Hope this helps. Please reach out for further assistance.
Thank you.

[JānisBojars · Accepted solution]

Sorry for late answer. Was on vacation.

We decided to transform data on DWH side. Calculate location for each Container at the end of each month and at the lates day of current month. This solution works well for us.

[v-veshwara-msft]

Hi @JānisBojars ,

Thanks for reaching out to Microsoft Fabric Community.
Just wanted to check if the response provided by @FBergamaschi was helpful. If further assistance is needed, please reach out.
Thank you.

[FBergamaschi]

Hi @JānisBojars,

you are already able to dinamically filter, since you have your measure working fine. Point seems to be show all the filtered lines in a table matrix at the same time. I am not sure on the limit that the table visual has, but it must be lower than the nr you have I conclude.

To understand how to help you, why do you need to show all of them (thousands and thounsands I imagine), at the same time in the table visual?

Thanks

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

[JānisBojars]

We decided to transform data on DWH side. Calculate location for each Container at the end of each month and at the lates day of current month. This solution works well for us.