If HasOneValue with && Return statement

[Anonymous]

Hello Power BI Super Users,

The data below is fake but the problem is real. 

October CoP File.pbix

My issue is that Excel Power Pivot does not use SelectedValue. I am having trouble adapting the return statement when I use HasOnveValue because I use &&.

Works Well in Power BI but not available in Excel

Masked Name =

VAR AttendanceValue = SELECTEDVALUE(Table1[Attendance Percent])

VAR GetName = SELECTEDVALUE(Table1[Student Full Name])

RETURN IF(AttendanceValue < 0.8 && AttendanceValue > 0, "*", GetName)

Get an error with HasOneValue

Masked Name1 =

VAR AttendanceValue = HASONEVALUE(Table1[Attendance Percent])

VAR GetName = HASONEVALUE(Table1[Student Full Name])

RETURN IF(HASONEVALUE(Table1[Attendance Percent]),

IF(VALUES(AttendanceValue < 0.8 && AttendanceValue > 0, "*", GetName)))

Here is a link to the PBIX file.

October CoP File.pbix

Appreciate any nudge. 

[EylesIT · Accepted solution]

@Anonymous, please try changing the DAX expression of your [Masked Name1] measure to this:

Masked Name1 = 
VAR ovAttendanceValue = HASONEVALUE(Table1[Attendance Percent])
VAR AttendanceValue = VALUES(Table1[Attendance Percent])
VAR GetName = VALUES(Table1[Student Full Name])
RETURN 
    IF(
        ovAttendanceValue,
        IF(
            AttendanceValue < 0.8 && AttendanceValue > 0, "*", GetName
        )
    )

The error you were getting was because you were trying to pass in an expression to the VALUES function, but the VALUES function can only take a table or column. You were also trying to pass in more than one paramter when VALUES tables just 1 paramter.

[Anonymous]

@EylesIT - thank you!

I see what you mean. 

By using the nested IF statement, I could maintain the parameter rules and produce the intended output.

The if statement now has the parameter to choose either the hasonevalue or the alternative values.

Brilliant.

Thank you again.