Forum Discussion
Solved
Conditionally Select A Table give me error message
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 divis...
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.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 )
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 ) )
)
Thank you very mcuh. it works for me.
