Trying to filter a table based on the value selected in a slicer

[ChitraSamuel]

Measure 5 = VAR _Checkbox = SELECTEDVALUE (Slicer[Status])

RETURN IF(_Checkbox = "Yes",CALCULATE(fact_daily_employee_leave_balances,FILTER(fact_daily_employee_leave_balances,fact_daily_employee_leave_balances[Measure] = 1)),CALCULATE(fact_daily_employee_leave_balances,FILTER(fact_daily_employee_leave_balances,fact_daily_employee_leave_balances[Measure] = 0))

)

Can somebody tell me what is wrong with the above it gives me a "Multiple columns cannot be converted to a scalar value" error

[v-jayw-msft · Accepted solution]

Hi @ChitraSamuel ,

The formula returns a list while the measure need the result to be a single value that's why it returns the error message. In this case, either you create a new table instead of a measure, or change your formula to return a single value.

You may consider to modify the measure like below and add it to visual filter and set it = 1.

Measure 5 =
VAR _Checkbox =
    SELECTEDVALUE ( Slicer[Status] )
RETURN
    IF (
        _Checkbox = "Yes"
            && fact_daily_employee_leave_balances[Measure] = 1,
        1,
        IF ( fact_daily_employee_leave_balances[Measure] = 0, 1, 0 )
    )

Best Regards,

Jay

[ChitraSamuel]

Thank you very much.

Thats what i did. 

[SanketBhagwat]

Hi @ChitraSamuel 

You have mentioned 'fact_daily_employee_leave_balances' under your CALCULATE function.

I think that is the name of your table.
You will need to define a calculation under your CALCULATE function.
For example, 
test=CALCULATE(Sum(Table[Sales]),FILTER(Table,Employee="A")

If this post helps, then mark it as "Accept as Solution" and give it a thumbs up.

[ChitraSamuel]

Thanks Sanket,

To explain my scenario better

I have a Table in my power BI visualisation that shows the employee and their various leaves like long service leave, RDO's, Annual Leave etc. I have a parameter that allows the user to specify an excess measure. If the annual leave of an employee is more than than that specified value in the excess measure i want to filter the employees to show only those employees whose leave is more than the excess. For eg, if the excess specified is 150, then i want the employees in the table filtered to employees who have annual leave more than the specified value.

I have a slicer over this measure, that allows the user to choose if they want to see all employees or employees satisfying the above measure.

The measure column in the table is a value that sets individual rows to say if their annual leave is above the 150 mentioned in the parameter. The status slicer is to either show all employees or employees satisfying the above parameter

The calculate function returns a table and it is getting assigned to a measure which is a scalar value and hence it gives me a "Multiple columns cannot be converted to a scalar value" error. I understand from the error that the test variable in your calculation is a scalar measure and the calculate is returning a table with multiple columns test(Scalar) = Calculate(table) and hence i get an error. Is there some way i can associate. I hope i have been able to articulate myself.

[ChitraSamuel]

@SanketBhagwat 

I changed the measure calculation to the below

Measure 5 = VAR _Checkbox = SELECTEDVALUE (Slicer[Status])

Var _YES = CALCULATE(fact_daily_employee_leave_balances,FILTER(fact_daily_employee_leave_balances,fact_daily_employee_leave_balances[Measure] = 1))

Var _NO = CALCULATE(fact_daily_employee_leave_balances,FILTER(fact_daily_employee_leave_balances,fact_daily_employee_leave_balances[Measure] = 0))

RETURN IF(_Checkbox = "Yes",_YES,_NO)

[SanketBhagwat]

You again repeated the same mistake by not specifying anytging under your CALCULATE function.
Try using VALUES(Table'Column name') inside your CALCULATE.

If you want to return Employee Names then specify CALCULATE(VALUES(Table'[EmmployeeName]))


Thanks,
Sanket

[ChitraSamuel]

@SanketBhagwat It gives me "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". I can understand why the above error arises, but do not have a solution