Forum Discussion
Values function works with separate table but not within same function
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?
3 Replies
- Jihwan_KimSuper User
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] )- theaddiesFrequent Visitor
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.
- Jihwan_KimSuper User
Hi,
Thank you for your message.
Could you please share your sample pbix file's link?