Forum Discussion

theaddies's avatar
theaddies
Frequent Visitor
3 years ago

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

  • 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]
        )
    
    • theaddies's avatar
      theaddies
      Frequent 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_Kim's avatar
        Jihwan_Kim
        Super User

        Hi,

        Thank you for your message.

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