Conditionally Select A Table give me error message

[jimmyhua]

I created two tables one is for All divisions/ entire organization and one for divisions when one division is selected.  I have a variable called DivisionFilter using HASONEVALUE to detect if a division is selected.  if nothing is selected, I want to use the All division table to do a Top 10, otherwise I will use the division table to show divisional Top 10. 

When I use if statement below, I got a error message saying "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".  

How do I fix it.  Thanks.

IF(
DivisionFilter,
FILTER(RankedDivision, [RankByBL] <= 10), // Top 10 within each division if filtered
TOPN(10, RankedAll, [Backlog], DESC) // Top 10 for the entire organization if not filtered
)

[Irwan · Accepted solution]

hello @jimmyhua 

that happens because both FILTER() and TOPN() will give table (multiple value) as return value where your result needs to be a scalar (one value).

i believe FILTER() and TOPN() need another function to return as scalar.

here is a simple examples in form of measure.

Filter =
IF(
    ISFILTERED('Table'[Column2]),
    CALCULATE(
        MAX('Table'[Column1]),
        FILTER(
            'Table',
            'Table'[Index]>=1&&'Table'[Index]<=10
        )
    ),
    SELECTEDVALUE('Table'[Column1])
)

- unselect (return all value)

- selected (return value with index 1 to 10)

Hope this will help.
Thank you.

[Sahir_Maharaj · Accepted solution]

Hello @jimmyhua,

Can you please try this approach:

Top10Table = 
IF(
    HASONEVALUE(DivisionTable[Division]), 
    FILTER(RankedDivision, [RankByBL] <= 10), // Top 10 for selected division
    TOPN(10, RankedAll, [Backlog], DESC) // Top 10 for the entire organization
)

[Anonymous]

Hi @jimmyhua 

Do the methods solve your problem? If so, could you please mark helpful answers as solutions? This will help more users who are facing the same or similar difficulties. Thank you!

If there are still problems, please feel free to let me know.

Best Regards,
Yulia Xu

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

expected result measure: =
VAR _t =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( billing_fact ),
            division_dimension[division],
            billing_fact[billing]
        ),
        "@amount", CALCULATE ( SUM ( billing_fact[amount] ) )
    )
RETURN
    CALCULATE (
        SUM ( billing_fact[amount] ),
        KEEPFILTERS ( TOPN ( 10, _t, [@amount], DESC ) )
    )

[jimmyhua]

Thank you very mcuh.  it works for me.

[Irwan · Accepted solution]

hello @jimmyhua 

that happens because both FILTER() and TOPN() will give table (multiple value) as return value where your result needs to be a scalar (one value).

i believe FILTER() and TOPN() need another function to return as scalar.

here is a simple examples in form of measure.

Filter =
IF(
    ISFILTERED('Table'[Column2]),
    CALCULATE(
        MAX('Table'[Column1]),
        FILTER(
            'Table',
            'Table'[Index]>=1&&'Table'[Index]<=10
        )
    ),
    SELECTEDVALUE('Table'[Column1])
)

- unselect (return all value)

- selected (return value with index 1 to 10)

Hope this will help.
Thank you.

[jimmyhua]

Thank you.  This one works.