Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

If HasOneValue with &&& Return statement

 

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's avatar
    EylesIT
    2 years ago

    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.

     

2 Replies

  • EylesIT's avatar
    EylesIT
    Resolver II
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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.