topic Re: Values function works with separate table but not within same function in DAX Commands and Tips

Values function works with separate table but not within same function

theaddies — Tue, 18 Jul 2023 14:06:38 GMT

I have a table within Power BI that I created using the formula below.

top_10_country_list_alternative = topn( 10, values(capacity_production_by_plant[Country]), calculate(sum(capacity_production_by_plant[production]), year(capacity_production_by_plant[year]) = 2023) )

When I use this table in the formula below everything works fine.

filtered production test = sumx( filter( capacity_production_by_plant, capacity_production_by_plant[Country] in values(top_10_country_list_alternative[Country]) ), capacity_production_by_plant[production] )

However, I am unable to integrate to the two formulas together so that I do not need to create the additional table. The code below does not work. I get an error 'The values function expects a column reference expression or a table reference expression for argument '1''

filtered production test = var country_list = topn( 10, values(capacity_production_by_plant[Country]), calculate(sum(capacity_production_by_plant[production]), year(capacity_production_by_plant[year]) = 2023) ) return sumx( filter( capacity_production_by_plant, capacity_production_by_plant[Country] in values(country_list) ), capacity_production_by_plant[production] )

How do I go about integrating these two functions so I do not need to create a separate table?

Re: Values function works with separate table but not within same function

Jihwan_Kim — Tue, 18 Jul 2023 14:11:18 GMT

Hi,

I am not sure how your datamodel looks like, but please try something like below.

filtered production test = VAR country_list = SUMMARIZE ( TOPN ( 10, ALL ( capacity_production_by_plant[Country] ), CALCULATE ( SUM ( capacity_production_by_plant[production] ), YEAR ( capacity_production_by_plant[year] ) = 2023 ) ), capacity_production_by_plant[Country] ) RETURN SUMX ( FILTER ( capacity_production_by_plant, capacity_production_by_plant[Country] IN country_list ), capacity_production_by_plant[production] )

Re: Values function works with separate table but not within same function

theaddies — Tue, 18 Jul 2023 14:19:56 GMT

I don't get an error from this but it returns the sum of all production for the specfic year ignoring the top_10_country_list.

Re: Values function works with separate table but not within same function

Jihwan_Kim — Tue, 18 Jul 2023 14:39:54 GMT

Hi,

Thank you for your message.

Could you please share your sample pbix file's link?