Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
smath
Frequent Visitor

What's wrong in my measure? HELP!

I'm triying to do this measure 

 

metas rotacion = var area = VALUES('prebel area_nomina'[abr])
return IF( VALUES('prebel empleados'[empresa])<>"PREBEL" && [% Retirados acumulados]>0 && [Retirados]>0;
0,3 ;
IF(NOT(ISERROR(area));
IF([Retirados acumulados]>0;
SWITCH(area;
"PA"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=83;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date]))));
"PI"; IF(ISERROR(VALUES('prebel cecos'[Procesos Rotacion PI])) && [% Retirados acumulados]>0 ;
CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=84;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date])))));
"PO"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=85;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date])))
)
;0)
)
)
)

 

 

 

But Power BI shows me an error "mDXsCRIPT(Model) (21 ,12) Calculation error in measure 'preber metas[metas rotacion]: A table of multiple values was supplied where a single value was expected"

 

What's wrong in my measure? Help me please

 

 

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@smath,

This issue is caused by that you use VALUES()  in your DAX. VALUES() funtion returns a one-column table that contains the distinct values from the specified table or column.  Change your DAX to the following and check if you get expected result.

metas rotacion = var area = FIRSTNONBLANK('prebel area_nomina'[abr];1)
return IF( FIRSTNONBLANK('prebel empleados'[empresa];1)<>"PREBEL" && [% Retirados acumulados]>0 && [Retirados]>0;
0,3 ;
IF(NOT(ISERROR(area));
IF([Retirados acumulados]>0;
SWITCH(area;
"PA"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=83;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date]))));
"PI"; IF(ISERROR(FIRSTNONBLANK('prebel cecos'[Procesos Rotacion PI];1)) && [% Retirados acumulados]>0 ;
CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=84;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date])))));
"PO"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=85;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date])))
)
;0)
)
)
)



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuezhe-msft
Employee
Employee

@smath,

This issue is caused by that you use VALUES()  in your DAX. VALUES() funtion returns a one-column table that contains the distinct values from the specified table or column.  Change your DAX to the following and check if you get expected result.

metas rotacion = var area = FIRSTNONBLANK('prebel area_nomina'[abr];1)
return IF( FIRSTNONBLANK('prebel empleados'[empresa];1)<>"PREBEL" && [% Retirados acumulados]>0 && [Retirados]>0;
0,3 ;
IF(NOT(ISERROR(area));
IF([Retirados acumulados]>0;
SWITCH(area;
"PA"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=83;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date]))));
"PI"; IF(ISERROR(FIRSTNONBLANK('prebel cecos'[Procesos Rotacion PI];1)) && [% Retirados acumulados]>0 ;
CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=84;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date])))));
"PO"; CALCULATE(AVERAGE('prebel metas'[Valor])/100;'prebel metas'[Id_Indicador]=85;
FILTER('prebel metas'; 'prebel metas'[Fecha_Inicio]<=CALCULATE(MAX('prebel calendario'[db_date]))); FILTER('prebel metas'; 'prebel metas'[Fecha_Fin]>= CALCULATE(MIN('prebel calendario'[db_date])))
)
;0)
)
)
)



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia, effectively this is a solution to my problem.

Thank u very much, was a super solutior.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.