How to pass dimension value to a measure in set analysis within a straight table

[sabreyals]

Hi All,

   I have the following data and scenario.

Data:

Mkt_Name	Vintage	Measure
Market A	1	20
Market A	2	30
Market B	1	10
Market C	1	10
Market C	2	12
Market C	3	15
Market C	4	40

I need to report the measure for each market on its max vintage within a straight table

Mkt_Name	Vintage	Measure
Market A	2	30
Market B	1	10
Market C	4	40

If i use sum({<Vintage={$(=Max(Vintage))}>}Measure) it always sees 4 as the max vintage and returns  a 0 for Markets A&B.I also tried aggr(sum({<Vintage={$(=Max(Vintage))}>}Measure),Mkt_Name) but got  a null.

Note that i am not allowed to drop any rows as they are needed for other sections of the dashboard.

For now, i am achieving this using a dedicated variable per market that identifies the max Vintage and using an expression per Mkt_Name via pick match

=Pick(match([MarketId],'1','2','3'),
Sum({<Vintage={'$(=$(vMaxVinA))'}>}Measure),
Sum({<Vintage={'$(=$(vMaxVinB))'}>}Measure),
Sum({<Vintage={'$(=$(vMaxVinC))'}>}Measure))

This is not an optimum solution as i can have unto 30+ markets

It would be ideal if there is a way to transfer the dimension value into the expression like sum({<Vintage={$(=Max(Vintage))},Mkt_Name={Column(1)}>}Measure).

Is there a way using parametirzed variables? Does anyone know the syntax or a better solution.

[sabreyals]

I tried to pass the dimension value like this using parameters

Sum({<Vintage={'$(=$(vMaxVinNum(Mkt_Name)))'}>}Measure)

where vMaxVinNum=max({<Market={$1}>}VintageNumber)

Still no luck.

[StarinieriG]

Hi,

try in this way 

If(Max(Vintage)=Max(total <Mkt_Name> Vintage),Sum(Measure))

[sabreyals]

This could be a solution for my scenario but i have only presented a simple version of my data set. If i were to apply all business rules and use the full measure formula this gives me a lot of weird values and is intensely performance heavy. Appreciate yous input. Let's wait to see if anyone has different ideas.