Dynamic calculated field from other table

[harry6810]

Hi there,

I will try to briefly describe what I want to achieve. So, I have 4 main datasets: - Sales report [Sales] - Product base [Product] - GFK data [GFK] - Price monitoring [prices] Each of those tables has common fields - processor, RAM, graphic card etc. Then I created relations in order to link all 4 main datasets, each parameter separately (GPU, CPU, RAM). Additionally, I created relations with next two parameters "series" and "vendor" in a separate table and I added a column with our serie which is the equivalent to a competing one. And now, I created a hitlist based on the GFK report using all the matched parameters. Based on the "series" table and "match" column, I added a column with our series to the created table.

Now the question is how to insert a calculated measure with minimum price [min. price] that will apply to our "match" product and not to the to the oryginal, competitor series? Filtering by manufacturer, of course, returns an empty value.

This should work as follows: - find a product series from the series table [series]'series1' and assign an equivalent from the match column [series]'match' and then get the minimum price [prices][min. price]* for a specific config back from our series from [series]'series1'.

[min. price] is a measure: [min. price] = CALCULATE(MIN('prices'[lowest_price]),LASTDATE('prices'[data]))

[v-cazheng-msft · Accepted solution]

Hi @harry6810 

You may try a measure like this.

Min_Price =

CALCULATE (

    MIN([min. price]),

    FILTER (

        'Product base',

        'Product base' [Product] = SELECTEDVALUE('separate table'[Match])

    ),

    TREATAS ( VALUES ( 'Product base' [Product]), 'separate table'[Match] )

)

If you still have issue on it, could you please provide some sample data and show us how you create relationships for these tables with a screenshot to make it more clear so we can find a solution for you ? Thanks in advance!

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it, please feel free to let us know. Thanks a lot!